Skip Headers

Oracle® Objects for OLE Developer's Guide 10g Release 2 (10.2) Part Number B14309-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 10 Server Methods

This chapter describes the Oracle Objects for OLE Server methods.

For an introduction to OO4O server objects, see "Oracle Objects for OLE In-Process Automation Server" .

This chapter contains these topics:

- Server Methods: A to B

- Server Methods: C

- Server Methods: D to H

- Server Methods: I to L

- Server Methods: M to S

- Server Methods: T to Z

Server Methods: A to B

- Abs Method

- Add Method

- Add (OraIntervalDS) Method

- Add (OraIntervalYM) Method

- Add (OraNumber) Method

- Add (OraSubscriptions Collection) Method

- AddIntervalDS Method

- AddIntervalYM Method

- AddNew Method

- AddTable Method

- Append (OraCollection) Method

- Append (OraLOB) Method

- AppendChunk Method

- AppendChunkByte Method

- AQAgent (OraAQMsg) Method

- AQMsg (OraAQ) Method

- ArcCos (OraNumber) Method

- ArcSin (OraNumber) Method

- ArcTan (OraNumber) Method

- ArcTan2 (OraNumber) Method

- Attribute (OraMetaData) Method

- AutoBindDisable Method

- AutoBindEnable Method

- BeginTrans Method

Server Methods: C

- Cancel Method

- CancelEdit (OraRef) Method

- Ceil (OraNumber) Method

- ChangePassword (OraServer) Method

- ChangePassword (OraSession) Method

- Clone Method

- Clone (OraLOB/BFILE) Method

- Clone (OraCollection) Method

- Clone (OraIntervalDS) Method

- Clone (OraIntervalYM) Method

- Clone (OraNumber) Method

- Clone (OraObject/Ref) Method

- Clone (OraTimeStamp) Method

- Clone (OraTimeStampTZ) Method

- Close Method

- Close (OraBFILE) Method

- CloseAll (OraBFILE) Method

- CommitTrans Method

- Compare (OraLOB) Method

- ConnectSession Method

- CopyToClipboard Method

- Copy (OraLOB) Method

- CopyFromFile (OraLOB) Method

- CopyFromBFILE (OraLOB) Method

- CopyToFile (OraLOB/BFILE) Method

- Cos (OraNumber) Method

- CreateAQ Method

- CreateCustomDynaset Method

- CreateDatabasePool Method

- CreateDynaset Method

- CreateIterator Method

- CreateNamedSession Method

- CreateOraIntervalDS Method

- CreateOraIntervalYM Method

- CreateOraNumber Method

- CreateOraObject (OraDatabase) Method

- CreateOraTimeStamp Method

- CreateOraTimeStampTZ Method

- CreatePLSQLCustomDynaset Method

- CreatePLSQLDynaset Method

- CreateSession Method

- CreateSQL Method

- CreateTempBLOB/CLOB Method

Server Methods: D to H

- Delete Method

- Delete (OraCollection) Method

- Delete (OraRef) Method

- DeleteIterator Method

- Dequeue (OraAQ) Method

- Describe Method

- DestroyDatabasePool Method

- DisableBuffering (OraLOB) Method

- Div (OraIntervalDS) Method

- Div (OraIntervalYM) Method

- Div (OraNumber) Method

- DynasetCacheParams Method

- Edit Method

- Edit (OraRef) Method

- ElementValue Method

- EnableBuffering (OraLOB) Method

- Enqueue (OraAQ) Method

- Erase (OraLOB) Method

- ExecuteSQL Method

- Exist (OraCollection) Method

- Exp (OraNumber) Method

- FetchOraRef Method

- FieldSize Method

- FindFirst, FindLast, FindNext, and FindPrevious Methods

- Floor (OraNumber) Method

- FlushBuffer (OraLOB) Method

- GetDatabaseFromPool Method

- GetChunk Method

- GetChunkByte Method

- GetChunkByteEx Method

- GetXML Method

- GetXMLToFile Method

- GetRows Method

- Get_Value Method

- HypCos (OraNumber) Method

- HypSin (OraNumber) Method

- HypTan (OraNumber) Method

Server Methods: I to L

- InitIterator Method

- IsEqual (OraIntervalDS) Method

- IsEqual (OraIntervalYM) Method

- IsEqual (OraNumber) Method

- IsEqual (OraTimeStamp) Method

- IsEqual (OraTimeStampTZ) Method

- IsGreater (OraIntervalDS) Method

- IsGreater (OraIntervalYM) Method

- IsGreater (OraNumber) Method

- IsGreater (OraTimeStamp) Method

- IsGreater (OraTimeStampTZ) Method

- IsLess (OraIntervalDS) Method

- IsLess (OraIntervalYM) Method

- IsLess (OraTimeStamp) Method

- IsLess (OraTimeStampTZ) Method

- IterNext Method

- IterPrev Method

- LastServerErrReset Method

- Ln (OraNumber) Method

- Log (OraNumber) Method

Server Methods: M to S

- MatchPos (OraLOB/BFILE) Method

- Mod (OraNumber) Method

- MonitorForFailover Method

- MonitorStart (OraAQ) Method

- MonitorStop (OraAQ) Method

- MoveFirst, MoveLast, MoveNext, and MovePrevious Methods

- MovePreviousn, MoveNextn, MoveRel, and MoveTo Methods

- Mul (OraIntervalDS) Method

- Mul (OraIntervalYM) Method

- Mul (OraNumber) Method

- Neg (OraIntervalDS) Method

- Neg (OraIntervalYM) Method

- Neg (OraNumber) Method

- Open (OraServer) Method

- Open (OraBFILE) Method

- OpenDatabase Method

- OriginalItem Method

- OriginalName

- Power (OraNumber) Method

- Put_Value Method

- Read (OraLOB/BFILE) Method

- ReadChunk Method

- Refresh Method

- Refresh (OraRef) Method

- Register Method

- Remove Method

- Remove (OraSubscriptions Collection) Method

- RemoveFromPool Method

- ResetTrans Method

- Rollback Method

- Round (OraNumber) Method

- SetPi (OraNumber) Method

- Sin (OraNumber) Method

- Sqrt (OraNumber) Method

- Sub (OraIntervalDS) Method

- Sub (OraIntervalYM) Method

- Sub (OraNumber) Method

Server Methods: T to Z

- Tan (OraNumber) Method

- ToDate Method

- ToOraNumber (OraIntervalDS) Method

- ToOraTimeStamp Method

- ToOraTimeStampLTZ Method

- ToOraTimeStampTZ Method

- ToUniversalTime Method

- Trim (OraCollection) Method

- Trim (OraLOB) Method

- Trunc (OraNumber) Method

- Unregister Method

- Update Method

- Update (OraRef) Method

- Write (OraLOB) Method

## Abs Method

Applies To

OraNumber Object

Description

Calculates the absolute value of an `OraNumber` object.

Usage

    OraNumber.Abs

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

## Add Method

Applies To

OraParameters Collection

Description

Adds a parameter to the `OraParameters` collection.

Usage

    oraparameters.Add Name, Value, IOType, ServerType, ObjectName

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`Name` | The name of the parameter to be added to the parameters collection. This name is issued both for parameter identification and as the placeholder in associated SQL and PL/SQL statements.
`Value` | A `Variant` specifying the initial value of the parameter. The initial value of the parameter is significant; it defines the data type of the parameter.
`IOType` | An integer code specifying how the parameter is to be used in SQL statements and PL/SQL blocks.
`ServerType` | Specifies Oracle Database type to which this parameter is to be bound. This is required when binding to `BLOB`, `CLOB`, `BFILE`, `OBJECT`, `REF`, `NESTED` `TABLE`, or `VARRAY`. For a list of possible values, see the `OraParameter` "ServerType Property".
`ObjectName` | A case-sensitive string containing the name of the `Object`. This is only required if `ServerType` is `ORATYPE_OBJECT`, `ORATYPE_VARRAY`, or `ORATYPE_TABLE`. `ServerType` is required for `ORATYPE_REF` when the `REF` is used in PL/SQL.

IOType Settings

The `IOType` settings are:

Settings | Values | Description
--- | --- | ---
`ORAPARM_INPUT` | 1 | Used for input variables only
`ORAPARM_OUTPUT` | 2 | Used for output variables only
`ORAPARM_BOTH` | 3 | Used for variables that are both input and output

These values can be found in the `oraconst.txt` file.

By default, the maximum size of the `ORAPARM_OUTPUT` variable for `ServerType` `VAR`, `VARCHAR2`, and `ORATYPE_RAW_BIN` is set to 128 bytes. Use the `MinimumSize` property to change this value. The minimum size of an `ORAPARM_OUTPUT` variable for `VAR` and `VARCHAR2` must always be greater than the size of the expected data from the database column.

Verify that this value is correct. If you set an incorrect option, such as `ORAPARM_BOTH` for the `IN` stored procedure parameter type, this can result in errors. `ORAPARM_BOTH` is for `IN` and `OUT` parameters only. It is not used against one stored procedure that has an `IN` parameter and another that has an `OUT` parameter. For this case, use two parameters. Errors caused this way are rare, if there is a parameter-related error, verify that the `IOType` is correct.

The `Value` argument can be an Oracle Database 10g object, such as an `OraBLOB`. Note that a copy of the object is made at that point in time and the `Value` property must be accessed to obtain a new object that refers to the value of the parameter. For example, if `IOType` is `ORATYPE_BOTH` and an `OraBLOB` obtained from a dynaset is passed in as the input value, the `Parameter` `Value` property needs to be accessed one time after the SQL has been executed to obtain the newly updated output value of the parameter. The object is obtained from the parameter in the same manner as from a dynaset.

The `Value` property always refers to the latest value of the parameter. The Visual Basic value `Null` can also be passed as a value. The Visual Basic `EMPTY` value can be used for `BLOB` and `CLOB` data types to mean an empty LOB, and the `EMPTY` value can be used for `OBJECT`, `VARRAY`, and `NESTED` `TABLE` data types to mean an object whose attributes are all `Null`.

Remarks

Use parameters to represent SQL bind variables (as opposed to rebuilding the SQL statement). SQL bind variables are useful because you can change a parameter value without having to parse the query again. Use SQL bind variables only as input variables.

You can also use parameters to represent PL/SQL bind variables. You can use PL/SQL bind variables as both input and output variables.

The `ORATYPE_RAW_BIN` `ServerType` value is used when binding to Oracle `Raw` columns. A byte array is used to `Put` or `Get` values. The maximum allowable size of an `ORATYPE_RAW_BIN` bind buffers is 2000 bytes when bound to a column of a table and 32 KB when bound to a stored procedure. For example code, see the samples in the `ORACLE_BASE\ORACLE_HOME``\OO4O\VB\Raw` directory.

Examples

This example demonstrates using the `Add` and `Remove` parameter methods, the `ServerType` parameter property, and the `ExecuteSQL` database method to call a stored procedure and function (located in `ORAEXAMP.SQL`). Copy and paste this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

    'Declare variables
    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'Add EMPNO as an Input/Output parameter and set its initial value.
    OraDatabase.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT
    OraDatabase.Parameters("EMPNO").ServerType = ORATYPE_NUMBER

    'Add ENAME as an Output parameter and set its initial value.
    OraDatabase.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT
    OraDatabase.Parameters("ENAME").ServerType = ORATYPE_VARCHAR2

    'Add SAL as an Output parameter and set its initial value.
    OraDatabase.Parameters.Add "SAL", 0, ORAPARM_OUTPUT
    OraDatabase.Parameters("SAL").ServerType = ORATYPE_NUMBER

    'Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME.
    ' This Stored Procedure can be found in the file ORAEXAMP.SQL.
    OraDatabase.ExecuteSQL ("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;")
    'Display the employee number and name.

    'Execute the Stored Function Employee.GetSal to retrieve SAL.
    ' This Stored Function can be found in the file ORAEXAMP.SQL.
    OraDatabase.ExecuteSQL ("declare SAL number(7,2); Begin" & _
               ":SAL:=Employee.GetEmpSal (:EMPNO); end;")

    'Display the employee name, number and salary.
    MsgBox "Employee " & OraDatabase.Parameters("ENAME").value & ", #" & _
              OraDatabase.Parameters("EMPNO").value & ",Salary=" & _
              OraDatabase.Parameters("SAL").value

    'Remove the Parameters.
    OraDatabase.Parameters.Remove "EMPNO"
    OraDatabase.Parameters.Remove "ENAME"
    OraDatabase.Parameters.Remove "SAL"

    End Sub

See Also: OraParameter Object Remove Method ServerType Property

## Add (OraIntervalDS) Method

Applies To

OraIntervalDS Object

Description

Adds an argument to the `OraIntervalDS` object.

Usage

    OraIntervalDS.Add operand

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `operand` | A `Variant` of type `String`, a numeric value, or an `OraIntervalDS` object to be added.

Remarks

The result of the operation is stored in an `OraIntervalDS` object, overwriting any previous value. There is no return value.

If `operand` is a `Variant` of type `String`, it must be in the following format: [+/-]Day HH:MI:SSxFF.

If `operand` is a numeric value, the value provided should represent the total number of days that the constructed `OraIntervalDS` object represents.

Examples

    Dim oraIDS as OraIntervalDS

    'Create an OraIntervalDS using a string which represents
    '1 day and 12 hours
    Set oraIDS = oo4oSession.CreateOraIntervalDS("1 12:0:0.0")

    'Add an interval using a string, which represents 2 days
    'and 12 hours, to oraIDS.
    'The resulting oraIDS is an interval which represents 4 days
    oraIDS.Add "2 12:0:0.0"

See Also: "CreateOraIntervalDS Method"

## Add (OraIntervalYM) Method

Applies To

OraIntervalYM Object

Description

Adds an argument to the `OraIntervalYM` object.

Usage

    OraIntervalYMObj.Add operand

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `operand` | A `Variant` of type `String`, a numeric value, or an `OraIntervalYM` object to be added.

Remarks

The result of the operation is stored in the `OraIntervalYM` object, overwriting any previous value. There is no return value.

If `operand` is a `Variant` of type `String`, it must be in the following format: [+/-]YEARS-MONTHS.

If `operand` is a numeric value, the value provided should represent the total number of years that the constructed `OraIntervalYM` object represents.

Examples

    Dim oraIYM as OraIntervalYM

    'Create an OraIntervalYM using a string which represents 1 year and 6 months
    Set oraIYM = oo4oSession.CreateOraIntervalYM("1-6")

    'Add an interval using a string, which represents 2 years
    'and 6 months, to oraIYM.
    'The resulting oraIYM is an interval which represents 4 years
    oraIYM.Add "2-6"

See Also: CreateOraIntervalYM Method

## Add (OraNumber) Method

Applies To

OraNumber Object

Description

Adds a numeric argument to the `OraNumber` object.

Usage

    OraNumber.Add operand

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `operand` | A `Variant` of type `String`, `OraNumber` object, or a numeric value.

Remarks

The result of the operation is stored in an `OraNumber` object. There is no return value.

## Add (OraSubscriptions Collection) Method

Applies To

OraSubscriptions Collection

Description

Adds a subscription to the `OraSubscriptions` collection.

Usage

    orasubscriptions.Add Name, DbeventsHdl, Ctx

Arguments

The arguments for the method are:

Variants | Description
--- | ---
[`in`] `Name` | The database event of interest. The appropriate event trigger and AQ queue must be set up prior to this. `Name` refers to the subscription name in the form of the string `'SCHEMA.QUEUE'` if the registration is for a single consumer queue and `'SCHEMA.QUEUE:CONSUMER_NAME'` if the registration is for a multiple consumer queue. The `Name` string should be in uppercase.
[`in`] `DbeventsHdl` | The database event handler. An `IDispatch` interface implementing the `NotifyDBEvents` method, which is invoked when the database event of interest is fired.
[`in`] `Ctx` | Context-specific information that the application wants passed to the `NotifyDbEvents` method when it is invoked.

Remarks

To register for subscription of a database event, the name identifying the subscription of interest and the name of the `dbevent` handler that handles the event must be passed in when the `Add` method is called. The queues and event triggers necessary to support the database event must be set up before the subscriptions can be fired.

The `dbevent` handler should be an automation object that implements the `NotifyDBEvents` method.

NotifyDBEvents Handler

The `NotifyDBEvents` method is invoked by Oracle Objects for OLE when database events of interest are fired.

For more detailed information about setting up the queues and triggers for Oracle Database events, see to Triggers on System Events and User Events in Oracle Database Concepts.

The syntax of the method is:

    Public Function NotifyDBEvents(ByVal Ctx As Variant, ByVal Payload As Variant

Variants

The variants for the method are:

Variants | Description
--- | ---
`[in]` `Ctx` | Passed into the `OraSubscriptions.Add` method by the application. Context-sensitive information that the application wants passed on to the `dbevent` handler.
[in] `Payload` | The payload for this notification. Database events are fired by setting up event trigger and queues. `Payload` here refers to the payload, if any, that was enqueued in the queue when the event triggered.

Examples

Example: Registering an Application for Notification of Database Events

In the following example, an application subscribes for notification of database logon events (such as all logons to the database). When a user logs on to the database, the `NotifyDBEvents` method of the `DBEventsHdlr` that was passed in at the time of subscription is invoked. The context-sensitive information and the event-specific information are passed into the `NotifyDBEvents` method.

The `DBEventsHdlr` in this example is `DBEventCls`, which is defined later.

The main application:

    ' First instantiate the dbevent handler. The dbevent notification
    ' will fire the NotifyDBEvents on the callback handler.

    Public DBEventsHdlr As New DBEventCls
    Private Sub Form_Load()
        Dim gOraSession As Object
        Dim gOraSubscriptions As OraSubscriptions
        Dim gOraDatabase As OraDatabase

        'Create the OraSession Object
        Set gOraSession = CreateObject("OracleInProcServer.XOraSession")

       'Create the OraDatabase Object by opening a connection to Oracle.
        Set gOraDatabase = gOraSession.DbOpenDatabase
                 ("ora90.us.oracle.com", "pubsub/pubsub",
                  ORADB_ENLIST_FOR_CALLBACK)
        Set gOraSubscriptions = gOraDatabase.Subscriptions
        gOraSubscriptions.Add "PUBSUB.LOGON:ADMIN", DBEventsHdlr,
                 gOraDatabase
        gOraSubscriptions(0).Register
        MsgBox "OK"
    End Sub

The database event handler class that defines the `NotifyDBEvents` method.

    Public countofMsgs as integer
    Public Function NotifyDBEvents(Ctx As Variant, Payload As Variant )
        On error goto NotifyMeErr

        MsgBox "Retrieved payload " + Payload
       ' do something - here the subscription is unregistered after
       ' receiving 3 notifications
        countofMsgs = countofMsgs + 1
        If countofMsgs > 3 Then
            Ctx.Subscriptions(0).UnRegister
        End If
        Exit Sub
    NotifyMeErr:
        Call RaiseError(MyUnhandledError, "newcallback:NotifyMe Method")

    End Sub

See Also: "Database Events" for a complete discussion of the concepts involved in this example Triggers on System Events and User Events in Oracle Database Concepts for detailed information about setting up the queues and triggers for Oracle Database Events OraSubscription Object Remove (OraSubscriptions Collection) Method

## AddIntervalDS Method

Applies To

OraTimeStamp Object

OraTimeStampTZ Object

Description

Adds an interval that represents an interval from days to seconds, to the `OraTimeStamp` or `OraTimeStampTZ` object.

Usage

    OraTimeStampObj.AddIntervalDS operand
    OraTimeStampTZObj.AddIntervalDS operand

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `operand` | A `Variant` of type `String`, a numeric value, or an `OraIntervalDS` object that represents an interval from days to seconds to be added to the current `OraTimeStamp` or `OraTimeStampTZ` object.

Remarks

The result of adding an interval to the current `OraTimeStamp` or `OraTimeStampTZ` object is stored in the current object, overwriting any previous value. There is no return value.

If `operand` is a `Variant` of type `String`, it must be in the following format: [+/-] Day HH:MI:SSxFF.

If `operand` is a numeric value, the value provided should represent the total number of days that the constructed `OraIntervalDS` object represents.

Examples

Using OraTimeStamp

    Dim OraTimeStamp As OraTimeStamp

    ...

    'Create OraTimeStamp using a string
    Set OraTimeStamp = OraSession.CreateOraTimeStamp("2000-12-28 00:00:00", _
             "YYYY-MM-DD HH:MI:SS")

    'Add an interval using numeric value that represents 5 days and 12 hours
    OraTimeStamp.AddIntervalDS 5.5

    'Value should now be "2001-1-2 12:00:00"
    tsStr = OraTimeStamp.Value

Using OraTimeStampTZ

    Dim OraTimeStampTZ As OraTimeStampTZ

    ...

    'Create OraTimeStampTZ using a string
    Set OraTimeStamp = OraSession.CreateOraTimeStampTZ("2000-12-28 00:00:00 -07:00", _
           "YYYY-MM-DD HH:MI:SS TZH:TZM")

    'Add an interval using numeric value that represents 5 days and 12 hours
    OraTimeStampTZ.AddIntervalDS 5.5

    'Value should now be "2001-1-2 12:00:00"
    tstzStr = OraTimeStampTZ.Value

    ...

## AddIntervalYM Method

Applies To

OraTimeStamp Object

OraTimeStampTZ Object

Description

Adds an interval that represents an interval from years to months, to the `OraTimeStamp` or `OraTimeStampTZ` object.

Usage

    OraTimeStampObj.AddIntervalYM operand
    OraTimeStampTZObj.AddIntervalYM operand

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `operand` | A `Variant` of type `String`, a numeric value, or an `OraIntervalYM` object that represents an interval from years to months, to be added to the current `OraTimeStamp` or `OraTimeStampTZ` object.

Remarks

The result of adding an interval to the current `OraTimeStamp` or `OraTimeStampTZ` object is stored in the current object, overwriting any previous value. There is no return value.

If `operand` is a `Variant` of type `String`, it must be in following format: [+/-] YEARS-MONTHS.

If `operand` is a numeric value, the value provided should represent the total number of years that the constructed `OraIntervalYM` object represents.

Examples

Example: Using the OraTimeStamp Object

    Dim OraTimeStamp As OraTimeStamp

    ...
    'Create OraTimeStamp using a string
    Set OraTimeStamp = OraSession.CreateOraTimeStamp("2000-12-28 00:00:00", _
             "YYYY-MM-DD HH:MI:SS")

    'Add an interval using numeric value that represents 2 years
    OraTimeStamp.AddIntervalYM 2

    'Value should now be "2002-12-28 00:00:00"
    tsStr = OraTimeStamp.Value

    ...

Example: Using the OraTimeStampTZ Object

    Dim OraTimeStampTZ As OraTimeStampTZ

    ...
    'Create OraTimeStampTZ using a string
    Set OraTimeStampTZ =OraSession.CreateOraTimeStampTZ("2000-12-28 00:00:00" & _
                "-07:00"  "YYYY-MM-DD HH:MI:SS TZH:TZM")

    'Add an interval using numeric value that represents 2 years
    OraTimeStampTZ.AddIntervalYM 2

    'Value should now be "2002-12-28 00:00:00"
    tstzStr = OraTimeStampTZ.Value

    ...

## AddNew Method

Applies To

OraDynaset Object

Description

Clears the copy buffer and begins a record insertion operation into the specified dynaset and associated database.

Usage

    oradynaset.AddNew
    oradynaset.DbAddNew

Remarks

When an `AddNew` operation is initiated, values of fields present within the dynaset are maintained in a copy buffer and do not reflect the actual contents of the database.

The values of the fields are modified through the `OraField` object, and committed with an `Update` operation or when database movement occurs, which discards the new row. Field values that have not been explicitly assigned are either set to `Null` or allowed to default by way of the Oracle default mechanism, depending on the Column Defaulting mode of the options flag used when the `OpenDatabase` method was called. In either case, fields that appear in the database table but not in the dynaset are always defaulted by the Oracle default mechanism.

Internally, records are inserted by the `AddNew` method using the `"INSERT` `into` `TABLE` `(...)` `VALUES` `(...)"` SQL statement, and are added to the end of the table.

When adding a row that has object, collection, and `REF` columns, these column values should be set to a valid `OraObject`, `OraCollection`, or `OraRef` interface or to the `Null` value. The column values can also be set with the automation object returned by the `CreateOraObject` method. When adding a row having a `BLOB`, `CLOB`, or `BFILE` column, the column value should be set to a valid `OraBLOB`, `OraCLOB`, or `OraBFILE` interface, `Null`, or `Empty`. Setting a `BLOB`, `CLOB`, and `BFILE` column to an `Empty` value inserts an empty LOB value into the database.

Note: A call to `Edit`, `AddNew`, or `Delete` methods cancels any outstanding `Edit` or `AddNew` method calls before proceeding. Any outstanding changes not saved using an `Update` method are lost during the cancellation.

Examples

This example demonstrates the use of the `AddNew` and `Update` methods to add a new record to a dynaset. Copy this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

     'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")

     'Create the OraDatabase Object by opening a connection to Oracle.

     Set OraDatabase = OraSession.OpenDatabase("ExampleDb", _
                   "scott/tiger", 0&)

     'Create the OraDynaset Object.
     Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)
     'Begin an AddNew.
     OraDynaset.AddNew

     'Set the field(column) values.
     OraDynaset.Fields("EMPNO").Value = "1000"
     OraDynaset.Fields("ENAME").Value = "WILSON"
     OraDynaset.Fields("JOB").Value = "SALESMAN"

     OraDynaset.Fields("MGR").Value = "7698"
     OraDynaset.Fields("HIREDATE").Value = "19-SEP-92"
     OraDynaset.Fields("SAL").Value = 2000
     OraDynaset.Fields("COMM").Value = 500
     OraDynaset.Fields("DEPTNO").Value = 30

     'End the AddNew and Update the dynaset.
     OraDynaset.Update

     MsgBox "Added one new employee."

    End Sub

See Also: Delete Method Edit Method EditMode Property MoveFirst, MoveLast, MoveNext, and MovePrevious Methods Update Method Validate Event

## AddTable Method

Applies To

OraParameters Collection

Description

Adds an array parameter to the `OraParameters` collection.

Usage

    oraparamarray.AddTable  Name, IOType, ServerType, ArraySize , ElementSize, ObjectName

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`Name` | The name of the parameter to be added to the parameters collection. This name is used both for parameter identification and as the placeholder in associated SQL and PL/SQL statements.
`IOType` | An integer code specifying how the parameter is to be used in SQL statements and PL/SQL blocks.
`ServerType` | Specifies Oracle Database type to which this array parameter is to be bound. For a list of possible values, see the `OraParameter` ServerType Property.
`ArraySize` | Defines the number of elements in the parameter array. This parameter is used to calculate the maximum buffer length.
`ElementSize` [optional] | Defines the size of the element. Valid for only character and string type table (array) parameters. The valid size for `ElementSize` depends on the `VarType`. `ElementSize` is optional in all cases except when bound to `char` and `string` types.
`ObjectName` | A case-sensitive string containing the name of the `Object`. This is only required if `ServerType` is `ORATYPE_OBJECT`, `ORATYPE_VARRAY`, or `ORATYPE_TABLE`. It is required for `ORATYPE_REF` when the `REF` is used in PL/SQL.

IO Type Settings

The `IOType` settings are:

Constant | Value | Description
--- | --- | ---
`ORAPARM_INPUT` | 1 | Used for input variables only.
`ORAPARM_OUTPUT` | 2 | Used for output variables only.
`ORAPARM_BOTH` | 3 | Used for variables that are both input and output.

Verify that this value is correct. If you set an incorrect option, such as `ORAPARM_BOTH` for the stored procedure parameter type `IN`, this can result in errors. `ORAPARM_BOTH` is for `IN` and `OUT` parameters only. It is not used against one stored procedure that has an `IN` parameter and another that has an `OUT` parameter. In this case, use two parameters. Errors caused in this way are rare, but if there are parameter-related errors, verify that the `IOType` is correct.

Server Type

See ServerType Property for valid types and note the following:

Note:

- External data type `ORATYPE_NUMBER` allows decimal precision of `1` to `38`.

- The maximum positive number is `0.99999999999999999999` `E` `+` `38`.

- The minimum positive number is `0.1` `E``-38.`

- The minimum negative number is `-0.99999999999999999999` `E` `+` `38`.

- The maximum negative number is `0.1` `E` `-38`.

ElementSize (Optional)

Valid for character, string, and raw types. The valid size for `ElementSize` depends on the `VarType`. This represents the length of each individual string or raw array element. These ranges are listed.

VarType | Size
--- | ---
`ORATYPE_VARCHAR2` | Valid range from 1 to 1999
`ORATYPE_VARCHAR` | Valid range from 1 to 1999
`ORATYPE_STRING` | Valid range from 1 to 1999
`ORATYPE_CHAR` | Valid range from 1 to 255
`ORATYPE_CHARZ` | Valid range from 1 to 255
`ORATYPE_RAW_BIN` | Valid range from 1 to 4000 (see remarks)

Remarks

Use parameters to represent SQL bind variables for array insert, update, and delete operations, rather than rebuilding the SQL statement. SQL bind variables are useful because you can change a parameter value without having to parse the query again. Use SQL bind variables only as input variables.

You can also use parameters to represent PL/SQL bind (`IN`/`OUT`) variables. You can use PL/SQL bind variables as both input and output variables.

The `ServerType` value `ORATYPE_RAW_BIN` is used when binding to Oracle `Raw` columns. A byte array is used to `Put` or `Get` values. The maximum allowable size of `ORATYPE_RAW_BIN` bind buffers is 2000 bytes when bound to a column of a table: the maximum allowable size is 32 KB when bound to a stored procedure. No element (see `ElementSize` argument) can be greater than 4000 bytes when binding to stored procedures, 2000 bytes against columns of tables. For example code, see the samples in the `ORACLE_BASE\ORACLE_HOME``\OO4O\VB\Raw` directory.

Examples

See "Example: Using OraParamArrays with PL/SQL".

See Also: ServerType Property

## Append (OraCollection) Method

Applies To

OraCollection Object

Description

Extends the size of the collection by one and appends the `Variant` value at the end of the collection.

Usage

    OraCollection.Append element

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `element` | A `Variant` representing the value to be appended.

Remarks

If an `OraCollection` represents a collection of `Object` types or `Ref`s, the element argument should represent a valid `OraObject` or `OraRef`.

Examples

The following example illustrates the `Append` method. Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in OraCollection Examples".

Example: Append Method for the OraCollection Object Example

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim EnameList as OraCollection

    'create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create a dynaset object from department
    set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&)

    'retrieve a Enames column from Department.
    'Here Value property of OraField object returns EnameList OraCollection
    set EnameList = OraDynaset.Fields("Enames").Value

    'Append an "Eric" to the collection.
    'Before that row level lock should be obtained
    OraDynaset.Edit
    EnameList.Append "Eric"
    OraDynaset.Update

## Append (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

Description

Appends the LOB content of the input OraLOB object to the internal LOB value of this instance.

Usage

    OraBlob.Append srcBlob
    OraClob.Append srcClob

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `srcLOB` | A valid object of type `OraBLOB` or `OraCLOB`.

Remarks

Appends the LOB content of input LOB to the end of current LOB value. Obtain either a row-level lock or an object-level lock before calling this method.

## AppendChunk Method

Applies To

OraField Object

Description

Appends data from a string to a `LONG` or `LONG` `RAW` field in the copy buffer.

Usage

    orafield.AppendChunk(string)
    orafield.DbAppendChunk(string)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`string` | Data to append to the specified field.

Remarks

The `AppendChunk` method allows the manipulation of data fields that are larger than 64 KB.

Examples

Note: This example cannot be run as is. It requires a defined form named `frmChunk`.

This example demonstrates the use of the `AppendChunk` method to read a file into a `LONG` `RAW` column of a database. This example expects a valid dynaset named `OraDynaset` representing a table with a column named `longraw`. Copy this code into the definition section of a form named `frmChunk`. Call this procedure with a valid `filename`.

    Sub AppendChunkExample (FName As String)

     'Declare various variables.
     Dim NumChunks As Integer, RemChunkSize As Integer
     Dim TotalSize As Long, CurChunk As String
     Dim I As Integer, FNum As Integer, ChunkSize As Integer

     'Set the size of each chunk.
     ChunkSize = 10240

     frmChunk.MousePointer = HOURGLASS

     'Begin an add operation.
     OraDynaset.AddNew

     'Clear the LONGRAW field.
     OraDynaset.Fields("LONGRAW").Value = ""

     'Get a free file number.
     FNum = FreeFile

     'Open the file.
     Open FName For Binary As #FNum

     'Get the total size of the file.

     TotalSize = LOF(FNum)

     'Set number of chunks.
     NumChunks = TotalSize \ ChunkSize

     'Set number of remaining bytes.
     RemChunkSize = TotalSize Mod ChunkSize

     'Loop through the file.
     For I = 0 To NumChunks

      'Calculate the new chunk size.
      If I = NumChunks Then
       ChunkSize = RemChunkSize
      End If

      CurChunk = String$(ChunkSize, 32)

      'Read a chunk from the file.
      Get #FNum, , CurChunk

      'Append chunk to LONGRAW field.
      OraDynaset.Fields("LONGRAW").AppendChunk (CurChunk)

     Next I

    'Complete the add operation and update the database.
    OraDynaset.Update

     'Close the file.
     Close FNum

     frmChunk.MousePointer = DEFAULT

    End Sub

See Also: "Migration from LONG RAW to LOB or BFILE" FieldSize Method GetChunk Method Type Property

## AppendChunkByte Method

Applies To

OraField Object

Description

Appends data from a byte array to a `LONG` or `LONG` `RAW` field in the copy buffer.

Usage

    orafield.AppendChunkByte(ByteArray, numbytes)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`Byte` `Array` | Data to append to the specified field.
`numbytes` | Number of bytes to copy.

Remarks

The `AppendChunkByte` method allows the manipulation of data fields that are larger than 64 KB.

Examples

Note: This is an incomplete code sample, provided for your reference. A complete Visual Basic sample called `LONGRAW` that is based on this code sample, is provided in the OO4O samples directory.

This sample code demonstrates the use of the `AppendChunkByte` method to read a file into a `LONG` `RAW` column of a database. This code expects a valid dynaset named `OraDynaset` representing a table with a column named `longraw`.

    Sub AppendChunkByteExample (FName As String)

     'Declare various variables.
     Dim NumChunks As Integer, RemChunkSize As Integer
     Dim TotalSize As Long, CurChunkByte() As Byte
     Dim I As Integer, FNum As Integer, ChunkSize As Integer
     'Set the size of each chunk.
     ChunkSize = 10240
     frmChunk.MousePointer = HOURGLASS

     'Begin an add operation.
     OraDynaset.AddNew
     'Clear the LONGRAW field.
     OraDynaset.Fields("LONGRAW").Value = ""

     'Get a free file number.
     FNum = FreeFile

     'Open the file.
     Open FName For Binary As #FNum

     'Get the total size of the file.
     TotalSize = LOF(FNum)

     'Set number of chunks.
     NumChunks = TotalSize \ ChunkSize

     'Set number of remaining bytes.
     RemChunkSize = TotalSize Mod ChunkSize

     'Loop through the file.
     For I = 0 To NumChunks

      'Calculate the new chunk size.
      If I = NumChunks Then
       ChunkSize = RemChunkSize

      End If

      ReDim CurChunkByte(ChunkSize)

      'Read a chunk from the file.
      Get #FNum, , CurChunkByte

      'Append chunk to LONGRAW field.
      OraDynaset.Fields("LONGRAW").AppendChunkByte (CurChunkByte)
     Next I
    'Complete the add operation and update the database.
    OraDynaset.Update

     'Close the file.
     Close FNum

     frmChunk.MousePointer = DEFAULT

    End Sub

See Also: "Migration from LONG RAW to LOB or BFILE"

## AQAgent (OraAQMsg) Method

Applies To

OraAQMsg Object

Description

Creates an instance of the `OraAQAgent` for the specified consumer and adds it to the `OraAQAgent`s list of the message.

Usage

    Set agent = qMsg.AQAgent(name)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `name` | A `String` up to 30 bytes representing the name of the consumer of the message.
[`in`] [optional] `Address` | A 128-byte `String` representing the protocol specific address of a recipient, such as [`schema.`]`queue`[`@dblink`].

Remarks

The `OraAQAgent` object represents a message recipient and is only valid for queues that allow multiple consumers. Queue subscribers are recipients by default. Use this object to override the default consumers.

An `OraAQAgent` object can be instantiated by invoking the `AQAgent` method. For example:

    Set agent = qMsg.AQAgent(consumer)

The maximum number of agents that a message can support is 10.

The `AQAgent` method returns an instance of an `OraAQAgent` object.

Note: `Address` is not supported in this release, but is provided for future enhancements.

## AQMsg (OraAQ) Method

Applies To

OraAQ Object

Description

Creates an `OraAQMsg` for the specified options.

Usage

    Set qMsg = Q.AQMsg(msgtype, typename, schema)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `msgtype` | An `Integer` representing a `RAW` or user-defined type. Optional for `RAW` type. Possible values are: `ORATYPE_RAW` (`23`) - Message type is `RAW`. `ORATYPE_OBJECT` (`108`) - Message type is user-defined.
[`in`] `typename` | A `String` representing the name of the type. Optional for `RAW` type. Default is `'RAW'`.
[`in`] [optional] `schema` | A `String` representing the schema where the type is defined. Default is `'SYS'`.

Remarks

The method could be used as follows:

    set QMsg = Q.AQMsg(ORATYPE_OBJECT,"MESSAGE_TYPE","SCOTT")
    set QMsg = Q.AQMsg

## ArcCos (OraNumber) Method

Applies To

OraNumber Object

Description

Calculates the arc cosine of an `OraNumber` object. The result is in radians.

Usage

    OraNumber.ArcCos

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

This method returns an error if the `OraNumber` value is less than `-1` or greater than `1`.

## ArcSin (OraNumber) Method

Applies To

OraNumber Object

Description

Calculates the arc sine of an `OraNumber` object. Result is in radians.

Usage

    OraNumber.ArcSin

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

This method returns an error if the `OraNumber` object is less than `-1` or greater than `1`.

## ArcTan (OraNumber) Method

Applies To

OraNumber Object

Description

Calculates the arc tangent of an `OraNumber` object. Result is in radians.

Usage

    OraNumber.ArcTan

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

## ArcTan2 (OraNumber) Method

Applies To

OraNumber Object

Description

Calculates the arc tangent of two numbers using the `operand` provided. The result is in radians.

Usage

    OraNumber.ArcTan2 operand

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `operand` | A `Variant` of type `String`, `OraNumber`, or a numeric value.

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

This method returns an error if `operand` is zero.

## Attribute (OraMetaData) Method

Applies To

OraMetaData Object

Description

Returns the `OraMDAttribute` object at the specified `index`.

Usage

    Set OraMDAttribute = OraMetaData.Attribute(2)
    Set OraMDAttribute = OraMetaData.Attribute("AttributeName")

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `index` | An `Integer` index between `0` and `count-1`, or a `String` representing the name of an attribute.

Remarks

None.

See Also: OraMetaData Object for a list of possible attribute names

## AutoBindDisable Method

Applies To

OraParameter Object

OraParamArray Object

Description

Resets the `AutoBind` status of a parameter.

Usage

    oraparameter.AutoBindDisable

Remarks

If a parameter has `AutoBindDisabled` status, it is not automatically bound to a SQL or PL/SQL statement.

Examples

This example demonstrates the use of the `AutoBindDisable` and `AutoBindEnable` methods to prevent unnecessary parameter binding while creating various dynasets that use different parameters. Copy this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

     'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")

     'Create the OraDatabase Object by opening a connection to Oracle.
     Set OraDatabase = OraSession. OpenDatabase("ExampleDb", _
                  "scott/tiger", 0&)

     'Add the job input parameter with initial value MANAGER.
     OraDatabase.Parameters.Add "job", "MANAGER", 1

     'Add the deptno input parameter with initial value 10.
     OraDatabase.Parameters.Add "deptno", 10, 1

     'Disable the deptno parameter for now.
     OraDatabase.Parameters("deptno").AutoBindDisable

     'Create the OraDynaset Object using the job parameter.
     Set OraDynaset = OraDatabase.CreateDynaset("select * from emp" & _
                   "where job = :job", 0&)

     'Only employees with job=MANAGER will be contained in the dynaset.
     MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", " & _
                   "Job=" & OraDynaset.Fields("job").value

     'Enable the deptno parameter and disable the job parameter.
     OraDatabase.Parameters("deptno").AutoBindEnable
     OraDatabase.Parameters("job").AutoBindDisable

     'Create the OraDynaset Object using the deptno parameter.
     Set OraDynaset = OraDatabase.CreateDynaset("select * from emp" & _
                   "where deptno = :deptno", 0&)

     'Only employees with deptno=10 will be contained in the dynaset.
     MsgBox "Employee #" & OraDynaset.Fields("empno").value & "," & _
                    "DeptNo=" & OraDynaset.Fields("deptno").value

    End Sub

See Also: AutoBindEnable Method

## AutoBindEnable Method

Applies To

OraParameter Object

OraParamArray Object

Description

Sets the `AutoBind` status of a parameter.

Usage

    oraparameter.AutoBindEnable

Remarks

If a parameter has `AutoBindEnabled` status, it is automatically bound to a SQL or PL/SQL statement.

Examples

This example demonstrates the use of the `AutoBindDisable` and `AutoBindEnable` methods to prevent unnecessary parameter binding while creating various dynasets that use different parameters. Copy this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

     'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")

     'Create the OraDatabase Object by opening a connection to Oracle.
     Set OraDatabase = OraSession. OpenDatabase("ExampleDb", _
                      "scott/tiger", 0&)

     'Add the job input parameter with initial value MANAGER.
     OraDatabase.Parameters.Add "job", "MANAGER", 1

     'Add the deptno input parameter with initial value 10.
     OraDatabase.Parameters.Add "deptno", 10, 1

     'Disable the deptno parameter for now.
     OraDatabase.Parameters("deptno").AutoBindDisable

     'Create the OraDynaset Object using the job parameter.
     Set OraDynaset = OraDatabase.CreateDynaset("select * from emp" & _
                     "where job = :job", 0&)

     'Only employees with job=MANAGER will be contained in the dynaset.
     MsgBox "Employee #" & OraDynaset.Fields("empno").value & "," &  _
                     "Job=" & OraDynaset.Fields("job").value

     'Enable the deptno parameter and disable the job parameter.
     OraDatabase.Parameters("deptno").AutoBindEnable
     OraDatabase.Parameters("job").AutoBindDisable

     'Create the OraDynaset Object using the deptno parameter.
     Set OraDynaset = OraDatabase.CreateDynaset("select * from emp" & _
                      "where deptno = :deptno", 0&)

     'Only employees with deptno=10 will be contained in the dynaset.
     MsgBox "Employee #" & OraDynaset.Fields("empno").value & "," & _
                      "DeptNo=" & OraDynaset.Fields("deptno").value

    End Sub

See Also: AutoBindDisable Method

## BeginTrans Method

Applies To

OraConnection Object

OraDatabase Object

OraSession Object

Description

Begins a database transaction within the specified session.

Usage

    oraconnection.BeginTrans
    oradatabase.BeginTrans
    orasession.BeginTrans

Remarks

After this method has been called, no database transactions are committed until a `CommitTrans` is issued. Alternatively, the session can be rolled back using the `Rollback` method. If a transaction has already been started, repeated use of the `BeginTrans` method causes an error.

If `Update` or `Delete` methods fail on a given row in a dynaset in a global transaction after you issue a `BeginTrans`, be aware that locks remain on those rows on which you called the `Update` or `Delete` method. These locks persist until you call a `CommitTrans` or `Rollback` method.

Note: If an `OraDatabase` object has been enlisted with Microsoft Transaction Server (MTS) and is part of a global MTS transaction, this method has no effect.

Examples

This example demonstrates the use of the `BeginTrans` method to group a set of dynaset edits into a single transaction and uses the `Rollback` method to cancel those changes. Copy this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

     'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset
     Dim fld As OraField

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")

     'Create the OraDatabase Object by opening a connection to Oracle.
     Set OraDatabase = OraSession. OpenDatabase("ExampleDb", _
                         "scott/tiger", 0&)

     'Create the OraDynaset Object.
     Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)

     'Start Transaction processing.
     OraSession.BeginTrans

     'Setup a field object to save object references.
     Set fld = OraDynaset.Fields("sal")

     'Traverse until EOF is reached, setting each employees salary to zero
     Do Until OraDynaset.EOF = True
       OraDynaset.Edit
       fld.value = 0
       OraDynaset.Update
       OraDynaset.MoveNext
     Loop
     MsgBox "All salaries set to ZERO."

     'Currently, the changes have NOT been committed to the database.
     'End Transaction processing. Using RollbackTrans
     'means the rollback can be canceled in the Validate event.
     OraSession.Rollback
     'MsgBox "Salary changes rolled back."

    End Sub

See Also: AutoCommit Property CommitTrans Method ResetTrans Method Rollback Method "Microsoft Transaction Server Support"

## Cancel Method

Applies To

OraSQLStmt Object created with the `ORASQL_NONBLK` option

Description

Cancels the currently executing SQL operation.

Usage

    status = OraSQL.NonBlockingState
       if status = ORASQL_STILL_EXECUTING
    OraSQL.CancelEndif

Return Values

`ORASQL_SUCCESS(0)` - Any errors are thrown as exceptions.

See Also: "Asynchronous Processing"

## CancelEdit (OraRef) Method

Applies To

OraRef Object

Description

Unlocks the referenceable object in the database and cancels the object update operation.

Usage

    OraRef.CancelEdit

Remarks

Care should be taken before using this method; it cancels any pending transaction on the connection.

## Ceil (OraNumber) Method

Applies To

OraNumber Object

Description

Calculates the ceiling value of an `OraNumber` object.

Usage

    OraNumber.Ceil

Remarks

The result of the operation is stored in an `OraNumber` object. There is no return value.

## ChangePassword (OraServer) Method

Applies To

OraServer Object

Description

Changes the password for a given user.

Usage

    OraServer.ChangePassword user_name, current_password, new_password

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `user_name` | A `String` representing the user for whom the password is changed.
[`in`] `current_password` | A `String` representing the current password for the user.
[`in`] `new_password` | A `String` representing the new password for whom the user account is set.

Remarks

The `OraServer` object should be attached to an Oracle database using the `Open` method before to using this method.

This method is useful when a password has expired. In that case, the `OpenDatabase` method could return the following error:

    ORA-28001 "the password has expired".

See Also: BeginTrans Method Close Method CommitTrans Method CreateAQ Method CreateCustomDynaset Method CreateTempBLOB/CLOB Method CreateDynaset Method CreateOraObject (OraDatabase) Method CreateSQL Method Describe Method ExecuteSQL Method FetchOraRef Method LastServerErrReset Method MonitorForFailover Method Open (OraServer) Method OpenDatabase Method RemoveFromPool Method Rollback Method

## ChangePassword (OraSession) Method

Applies To

OraSession Object

Description

Changes the password for a given user.

Usage

    OraSession.ChangePassword database_name, user_name, current_password, new_password

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `database_name` | A `String` representing the Oracle network specifier used when connecting to a database.
[`in`] `user_name` | A `String` representing the user for whom the password is changed.
[`in`] `current_password` | A `String` representing the current password for the user.
[`in`] `new_password` | A `String` representing the new password for whom the user account is set.

Remarks

This method is especially useful when a password has expired. In that case, the `OpenDatabase` or `CreateDatabasePool` method could return the following error:

    ORA-28001 "the password has expired".

Examples

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim password as String

    'Note: The DBA could expire scott's password by issuing
    'ALTER USER SCOTT PASSWORD EXPIRE

    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    password = "tiger"

    On Error GoTo err:
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/" & password, 0&)
    End

    err:
    'Check for password expiration error

    If OraSession.LastServerErr = 28001 Then
        OraSession.ChangePassword "ExampleDb", "scott", password, "newpass"
        'reset our password variable, then try OpenDatabase again
        password = "newpass"
        Resume
    End If

    End

See Also: OpenDatabase Method CreateDatabasePool Method

## Clone Method

Applies To

OraDynaset Object

Description

Returns a duplicate dynaset of the specified dynaset.

Usage

    Set oradynaset2 = oradynaset1.Clone
    Set oradynaset2 = oradynaset1.DbClone

Remarks

This method creates a duplicate dynaset of the one specified. The original and duplicate dynasets have their own current record. However, the new dynaset is not positioned on any row and has its `EOF` and `BOF` conditions set to `True`. To change this, you must explicitly set a current row on the new duplicate with a `Move` or `Find` method.

Using the `Clone` method has no effect on the original dynaset. You cannot add, update, or remove records from a dynaset clone.

Use the `Clone` method to perform an operation on a dynaset that requires multiple current records.

A cloned dynaset does not have all the property settings of the original. The `CacheBlock`, `CacheSliceSize`, `CacheSlicePerBlock`, and `FetchLimit` properties are all set to `Null`.

Bookmarks of a dynaset and its clone are interchangeable; bookmarks of dynasets created with separate `CreateDynaset` methods are not interchangeable.

See Also: Bookmark Property CreateDynaset Method

## Clone (OraLOB/BFILE) Method

Applies To

OraBLOB, OraCLOB Objects

OraBFILE Object

Description

Returns the clone of an OraLOB or `OraBFILE` object.

Usage

    OraBlob1 = OraBlob.Clone
    OraClob1 = OraClob.Clone
    OraBfile = OraBfile.Clone

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `OraLOB` | A valid object of type `OraBLOB`, `OraCLOB`, or `OraBFILE`.

Remarks

This method makes a copy of an `OraBLOB` or `OraCLOB` object. This copy does not change due to a dynaset move operation or `OraSQLStmt` `Refresh` operation. No operation that modifies the LOB content of an `OraBLOB` or `OraCLOB` object can be performed on a clone.

This method makes a copy of Oracle `BFILE` locator and returns an `OraBFILE` associated with that copy. The copy of an `OraBFILE` does not change due to a dynaset move operation or a `OraSQLStmt` refresh operation.

## Clone (OraCollection) Method

Applies To

OraCollection Object

Description

Returns the clone of an `OraCollection` object.

Usage

    set OraCollection1 = OraCollection.Clone

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `oraCollection1` | A valid `OraCollection` object

Remarks

This method makes a copy of an Oracle collection and returns an `OraCollection` object associated with that copy. This copy of an Oracle collection does not change due to a dynaset move operation or `OraSQLStmt` `Refresh` operation. An `OraCollection` object returned by this method allows operations to access its element values of the underlying Oracle collection and prohibits any operation that modifies its element values.

## Clone (OraIntervalDS) Method

Applies To

OraIntervalDS Object

Description

Returns a copy of the `OraIntervalDS` object.

Usage

    Set OraIntervalDSObjClone = OraIntervalDSObj.Clone

Remarks

Returns a new `OraIntervalDS` object with the same value as the original.

## Clone (OraIntervalYM) Method

Applies To

OraIntervalYM Object

Description

Returns a copy of the `OraIntervalYM` object.

Usage

    Set OraIntervalYMObjClone = OraIntervalYMObj.Clone

Remarks

Returns a new `OraIntervalYM` object with the same value as the original.

## Clone (OraNumber) Method

Applies To

OraNumber Object

Description

Returns a copy of the `OraNumber` object .

Usage

    Set OraNumber2 = OraNumber.Clone

Remarks

Returns a new `OraNumber` object with the same value as the original.

## Clone (OraObject/Ref) Method

Applies To

OraObject Object

OraRef Object

Description

Returns the clone of an `OraObject` or `OraRef` object.

Usage

    Set OraObjectClone = OraObject.CloneSet OraRefClone = OraRef.Clone

Remarks

This method makes a copy of a `Value` instance or `REF` value and returns an `OraObject` or `OraRef` object associated with that copy. This copy does not change due to a dynaset move operation or `OraSQLStmt` refresh operation. An `OraObject` object returned by this method allows an operation to access its attribute values of an underlying value instance and disallows any operation to modify its attribute values.

Examples

Before running the sample code, make sure that you have the necessary data types and tables in the database. For the following examples, see "Schema Objects Used in the OraObject and OraRef Examples"

Example: Clone Method for the OraObject Object

The following example shows the use of the `Clone` method.

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim Address as OraObject
    Dim AddressClone as OraObject

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create a dynaset object from person_tab
    set OraDynaset = OraDatabase.CreateDynaset("select * from person_tab",0&)

    'retrieve a address column from person_tab. Here Value property of OraField object
    'returns Address OraObject
    set Address = OraDynaset.Fields("Addr").Value

    'here Address OraObject points to Address value instance in the server
    'for the first row
    msgbox Address.Street

    'move to second row
    OraDynaset.MoveNext

    'here Address OraObject points to Address value instance in the server
    'for the second row
    msgbox Address.Street

    'get the clone of Address object. This clone points to the copy of
    'the value instance for second row
    set AddressClone = Address.Clone

    'move to third row
    OraDynaset.MoveNext

    'here Address OraObject points to Address value instance in the server
    'for third row
    msgbox Address.Street

    'here AddressClone OraObject points to copy of Address value instance
    ' in the server for second row
    msgbox AddressClone.Street

Example: Clone Method for the OraRef Object

The following example shows the usage of the `Clone` method. Before running the sample code, make sure that you have the necessary data types and tables in the database.

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim Person as OraRef
    Dim PersonClone as OraRef

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create a dynaset object from customers
    set OraDynaset = OraDatabase.CreateDynaset("select * from customers", 0&)

    'retrieve a aperson column from customers.
    'Here Value property of OraField object 'returns Person OraRef
    set Person = OraDynaset.Fields("aperson").Value

    'here Person OraRef points to Person Ref value in the server for the first row
    msgbox Person.Name

    'move to second row
    OraDynaset.MoveNext

    'here Person OraRef points to Person Ref value in the server for the second row
    msgbox Person.Name

    'get the clone of Person object.
    'This clone points to the copy of the Ref for second row
    set PersonClone = Person.Clone

    'move to third row
    OraDynaset.MoveNext

    'here Person OraRef points to Person Ref value
    'in the server for the third row
    msgbox Person.Name

    'here PersonClone OraRef points to Person Ref value
    'in the server for the second row
    msgbox PersonClone.Name

## Clone (OraTimeStamp) Method

Applies To

OraTimeStamp Object

Description

Returns a copy of the `OraTimeStamp` object.

Usage

    Set OraTimeStampObj1 = OraTimeStampObj.Clone

Remarks

Returns a new `OraTimeStamp` object with the same value as the current object.

## Clone (OraTimeStampTZ) Method

Applies To

OraTimeStampTZ Object

Description

Returns a copy of the `OraTimeStampTZ` object.

Usage

    Set OraTimeStampTZObj1 = OraTimeStampTZObj.Clone

Remarks

Returns a new `OraTimeStampTZ` object with the same value as the current object.

## Close Method

Applies To

OraDatabase Object

OraDynaset Object

OraSQLStmt Object

OraServer Object

Description

Does nothing. Added for compatibility with Visual Basic.

Remarks

Neither the `OraDatabase` nor the `OraDynaset` object supports this method. Once an `OraDatabase` or `OraDynaset` object has gone out of scope and there are no references to it, the object closes automatically.

See Also: CreateDynaset Method OpenDatabase Method

## Close (OraBFILE) Method

Applies To

OraBFILE Object

Description

Closes an opened `BFILE` data type.

Usage

    OraBfile = OraBfile.Close

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `OraBfile` | A valid object of type `OraBFILE`.

Remarks

This method only applies to `BFILE`s, not LOBs.

## CloseAll (OraBFILE) Method

Applies To

OraBFILE Object

Description

This method closes all open `OraBFILE` objects on this connection.

Usage

    OraBfile.CloseAll

## CommitTrans Method

Applies To

OraConnection Object

OraDatabase Object

OraSession Object

Description

Ends the current transaction and commits all pending changes to the database.

Usage

    oraconnection.CommitTrans
    oradatabase.CommitTrans
    orasession.CommitTrans

Remarks

The `CommitTrans` method acts differently for these objects:

- `OraConnection` and `OraDatabase`

  The `CommitTrans` method commits all pending transactions for the specified connection. This method has no effect if a transaction has not started. When a sessionwide transaction is in progress, you can use this method to commit the transactions for the specified connection prematurely.

- `OraSession`

  The `CommitTrans` method commits all transactions present within the session. The `CommitTrans` method is valid only when a transaction has been started. If a transaction has not been started, using the `CommitTrans` method causes an error.

Note: If an `OraDatabase` object has been enlisted with Microsoft Transaction Server (MTS) and is part of a global MTS transaction, this method has no effect.

Examples

This example demonstrates the use of the `BeginTrans` method to group a set of dynaset edits into a single transaction. `The CommitTrans` method then accepts the changes. Copy this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

     'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset

     Dim fld As OraField

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")

     'Create the OraDatabase Object by opening a connection to Oracle.
     Set OraDatabase = OraSession. OpenDatabase("ExampleDb", "scott/tiger", 0&)

     'Create the OraDynaset Object.
     Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)

     'Start Transaction processing.
     OraSession.BeginTrans

     'Setup a field object to save object references.
     Set fld = OraDynaset.Fields("sal")

     'Traverse until EOF is reached, setting each employees salary to zero.
     Do Until OraDynaset.EOF = True
       OraDynaset.Edit
       fld.value = 0
       OraDynaset.Update
       OraDynaset.MoveNext
     Loop
     MsgBox "All salaries set to ZERO."

     'Currently, the changes have NOT been committed
     'to the database.

     'End Transaction processing. Commit the changes to the database
     OraSession.CommitTrans
     MsgBox "Salary changes committed."

    End Sub

See Also: AutoCommit Property BeginTrans Method ResetTrans Method Rollback Method "Microsoft Transaction Server Support"

## Compare (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

OraBFILE Object

Description

Compares the specified portion of the LOB value of an `OraBLOB` or `OraCLOB` object (or `OraBFILE` object) to the LOB value of the input `OraBLOB` or `OraCLOB` object (or `OraBFILE` object).

Usage

    IsEqual = OraBlob.Compare srcBlob, amount, Offset, srcOffset
    IsEqual = OraClob.Compare srcClob, amount, Offset, srcOffset
    IsEqual = OraBfile.Compare srcBfile, amount, Offset, srcOffset

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `srcLOB` | Input `OraBLOB`, `OraCLOB`, or `OraBFILE` object whose value is to be compared.
[`in`] [optional] `amount` | An `Integer` specifying the number of bytes or characters to compare. The default value of `amount` is from the `Offset` to the end of each LOB.
[`in`] [optional] `Offset` | An `Integer` specifying the `1`-based `Offset` in bytes (`OraBLOB` or `OraBFILE`) or characters (`OraCLOB`) in the value of this object. Default value is `1`.
[`in`] [optional] `srcOffset` | An `Integer` specifying the `1`-based `Offset` in bytes (`OraBLOB` or `OraBFILE`) or characters (`OraCLOB`) in the value of the `srcLob` object. Default value is `1`.
[`out`] `IsEqual` | A Boolean representing the result of a compare operation.

Remarks

The `Compare` method returns `True` if comparison succeeds; otherwise, it returns `False`.

If the amount to be compared causes the comparison to take place beyond the end of one LOB but not beyond the end of the other, the comparison fails. Such a comparison could succeed only if the amount of data from the `Offset` to the end is the exactly the same for both LOBs.

This call is currently implemented by executing a PL/SQL block that utilizes `DBMS_LOB.INSTR()`.

## ConnectSession Method

Applies To

OraSession Object

Description

Returns the `OraSession` object with the specified name that is associated with the `OraClient` object of the specified session.

Usage

    Set orasession2 = orasession1.ConnectSession(session_name)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`session_name` | A `String` specifying the name of the session.

Remarks

This method is provided for simplicity and is equivalent to iterating through the `OraSessions` collection of the `OraClient` object of the current session and searching for a session named `session_name`. The `OraSessions` collection contains only sessions created through the current application. This means that it is not possible to share sessions across applications, only within applications.

Examples

This example demonstrates the use of the `ConnectSession` and `CreateNamedSession` methods to allow an application to use a session it previously created, but did not save. Copy this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

     'Declare variables
     Dim dfltsess As OraSession
     Dim OraSession As OraSession

     'Create the default OraSession Object.
     Set dfltsess = CreateObject("OracleInProcServer.XOraSession")

     'Try to connect to "ExampleSession". If it does not exist
     'an error is generated.
     On Error GoTo SetName
     Set OraSession = dfltsess.ConnectSession("ExampleSession")
     On Error GoTo 0

     'You can specify other processing here, such as creating a
     ' database and/or dynaset.

    Exit Sub

    SetName:
    'The session named "ExampleSession" was not found, so create it.
    Set OraSession = dfltsess.Client.CreateSession("ExampleSession")
    Resume Next

    End Sub

See Also: CreateSession Method OraClient Object OraSessions Collection

## CopyToClipboard Method

Applies To

OraDynaset Object

Description

Copy the rows from the dynaset to the clipboard in text format.

Usage

    OraDynaset.CopyToClipboard(NumOfRows, colsep, rowsep)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`NumOfRows` | Number of rows to be copied to the dynaset
`colsep` [optional] | Column separator in the `CHAR` data type to be inserted between columns
`rowsep` [optional] | Row separator in the `CHAR` data type to be inserted between rows

Remarks

This method is used to help transfer data between the Oracle Object for OLE cache (dynaset) and Windows applications, such as Excel or Word. The `CopyToClipboard` method copies data starting from the current position of the dynaset up to the last row.

The default column separator is TAB (ASCII 9).

The default row separator is ENTER (ASCII 13).

Examples

The following example copies data from the dynaset to the clipboard. Paste this code into the definition section of a form, then press F5.

    Sub Form_Load ()
     'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")
     'Create the OraDatabase Object by opening a connection to Oracle.
     Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
     Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)

    'Now call CopyToClipboard to copy the entire dynaset
     OraDynaset.CopyToClipboard -1, chr(9), chr(13)
    End Sub

## Copy (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

Description

Copies a portion of the internal LOB value of an input `OraBLOB` or `OraCLOB` object to internal LOB value of this instance.

Usage

    OraBlob.Copy srcBlob, amount, destOffset, srcOffset
    OraClob.Copy srcClob, amount, destOffset, srcOffset

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `srcLOB` | An `OraCLob` or `OraBLOB` object whose value is to be copied.
[`in`] [optional] `amount` | An `Integer` specifying number of bytes or characters to copy. Default value is the size of the `BLOB` or `CLOB` value of the `srcLOB` object.
[`in`] [optional] `destOffset` | An `Integer` specifying the offset in bytes or characters for the value of this object. Default value is `1`.
[`in`] [optional] `srcOffset` | An `Integer` specifying the offset in bytes or characters, for the value of the `srcLOB` object. Default value is `1`.

Remarks

Obtain either a row-level lock or object-level lock before calling this method.

## CopyFromFile (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

Description

Loads or copies a portion or all of a local file to the internal LOB value of this object.

Usage

    OraBlob.CopyFromFile "blob.bmp" amount, offset, chunksize
    OraClob.CopyFromFile "clob.txt" amount, offset, chunksize

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `filename` | A string specifying the absolute name and path for the file to be read.
[`in`] [optional] `amount` | An `Integer` specifying the maximum number in bytes to be copied. Default value is total file size.
[`in`] [optional] `offset` | An `Integer` specifying the absolute offset of the `BLOB` or `CLOB` value of this object, in bytes for `OraBLOB` or `OraBFILE` and characters for `OraCLOB`. Default value is `1`.
[`in`] [optional] `chunksize` | An `Integer` specifying the size for each read operation, in bytes. If `chunksize` parameter is not set or `0`, the value of the `amount` argument is used, which means the entire amount is transferred in one chunk.

Remarks

Obtain either a row-level lock or object-level lock before calling this method.

The file should be in the same format as the `NLS_LANG` setting.

Note: When manipulating LOBs using LOB methods, such as `Write` and `CopyFromFile`, the LOB object is not automatically trimmed if the length of the new data is smaller than the old data. Use the `Trim` (OraLOB) method to shrink the LOB object to the size of the new data.

Examples

Example: Using the CopyFromFile Method

This example demonstrates the use of the `CopyFromFile` method.

Be sure that you have the `PART` table in the database with valid LOB data in it. Also, be sure that you have installed the OraLOB Schema Objects as described in "Schema Objects Used in LOB Data Type Examples" .

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim PartImage as OraBLOB

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'Create a Dynaset containing a BLOB and a CLOB column
    set part = OraDatabase.CreateDynaset ("select * from part where" & _
                    "part_id = 1234",0)
    set PartImage = part.Fields("part_image").Value

    'copy the entire content of partimage.jpg file to LOBS
    part.Edit
    PartImage.CopyFromFile "partimage.jpg"
    part.Update

See Also: Trim (OraLOB) Method

## CopyFromBFILE (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

Description

Copies a portion or all of the LOB value of an `OraBFILE` object to the LOB value of this object.

Usage

    OraBlob.CopyFromBFile srcBFile, amount, destOffset, srcOffset

    OraClob.CopyFromBFile srcBFile, amount, destOffset, srcOffset

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `srcBFile` | An `OraBFILE` object from which the data is to be copied.
[`in`] [optional] `amount` | An `Integer` specifying the maximum number to be copied, in characters for `OraCLOB` or bytes for `OraBLOB` or `OraBFILE`. Default value is the size of `BFILE` value of the `srcBFile` object.
[`in`] [optional] `destOffset` | An `Integer` specifying the absolute offset for this instance. Default is `1`.
[`in`] [optional] `srcOffset` | An `Integer` specifying the absolute offset for the `BFILE` value of the source `OraBFILE` object. Default is `1`.

Remarks

Obtain either a row-level lock or object-level lock before calling this method.

For a single-byte character set, the `OraBFile` object should be of the same character set as the database.

If the database has a variable width character set, the `OraBFile` object passed to the `OraClob.CopyFromBFile` method must point to a file that uses the UCS2 character set.

## CopyToFile (OraLOB/BFILE) Method

Applies To

OraBLOB, OraCLOB Objects

OraBFILE Object

Description

Copies a portion or all of the internal LOB value of this object to the local file.

Usage

    OraBlob.CopyToFile "blob.bmp" amount,offset,chunksize
    OraClob.CopyToFile "clob.txt" amount,offset,chunksize
    OraBfile.CopyToFile "bfile.bmp" amount,offset,chunksize

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `filename` | A `String` specifying the absolute name and path for which the file is to be written.
[`in`] [optional] `amount` | An `Integer` specifying the maximum amount to be copied, in bytes for `OraBLOB`/`OraBFILE` and characters for `OraCLOB`. Default value is the size of the LOB or `BFILE`.
[`in`] [optional] `offset` | An `Integer` specifying absolute offset of the LOB or `BFILE` value of this instance, in bytes for `OraBLOB`/`OraBFILE` and characters for `OraCLOB`. Default value is `1`.
[`in`] [optional] `chunksize` | An `Integer` specifying the size, in bytes, for each write operation. If the `chunksize` parameter is not set or is `0`, the value of the `amount` argument is used which means the entire amount is transferred in one chunk.

Remarks

The file is in the same format as the `NLS_LANG` setting.

If the file exists, its contents is overwritten.

Examples

Example:Using the CopyToFile Method

This example demonstrates the use of the `CopyToFile` method.

Be sure that you have the `PART` table in the database with valid LOB data in it. Also, be sure that you have installed the OraLOB Schema Objects as described in "Schema Objects Used in LOB Data Type Examples" .

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim PartDesc as OraCLOB

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&

    'Create a Dynaset containing a BLOB and a CLOB column
    set part = OraDatabase.CreateDynaset ("select * from part where" & _
                    "part_id = 1234",0)
    set PartDesc = part.Fields("part_desc").Value

    'Copy the entire LOB content to partdesc.txt file
    PartDesc.CopyToFile "partdesc.txt"

## Cos (OraNumber) Method

Applies To

OraNumber Object

Description

Calculates the cosine of an `OraNumber` object given in radians.

Usage

    OraNumber.Cos

Remarks

The result of the operation is stored in an `OraNumber` object. There is no return value.

## CreateAQ Method

Applies To

OraDatabase Object

Description

Creates an instance of the `OraAQ` object.

Usage

    Set OraAq = OraDatabase.CreateAQ(Qname)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `Qname` | A `String` representing the name of the queue in the database.

Remarks

None.

## CreateCustomDynaset Method

Applies To

OraDatabase Object

Description

Creates a dynaset using custom cache and fetch parameters

Usage

    Set oradynaset = oradatabase.CreateCustomDynaset(sql_statement, options, slicesize, perblock, blocks, FetchLimit, FetchSize, SnapShotID)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`sql_statement` | Any valid Oracle SQL `SELECT` statement.
`slicesize` | Cache slice size.
`perblock` | Cache slices for each block.
`blocks` | Cache maximum number of blocks.
`FetchLimit` | Fetch array size.
`FetchSize` | Fetch array buffer size.
`options` | A bit flag indicating the status of any optional states of the dynaset. You can combine one or more options by adding their respective values. Specifying the constant `ORADYN_DEFAULT` or the value `&H0&` gives the following defaults for the dynaset: Behave like Visual Basic Mode for a database: Field values not explicitly set are set to `Null`, overriding database column defaults. Perform automatic binding of database parameters. Remove trailing blanks from character string data retrieved from the database. Create an updatable dynaset. Cache data on the client. Force a `MoveFirst` operation when the dynaset is created. Maintain read-consistency.
`SnapShotID` [optional] | The ID of a Snapshot obtained from the `SnapShot` property of an `OraDynaset`.

Constants

The following table lists constants and values for the options flag.

Constant | Value | Description
--- | --- | ---
`ORADYN_DEFAULT` | `&H0&` | Accept the default behavior.
`ORADYN_NO_AUTOBIND` | `&H1&` | Do not perform automatic binding of database parameters.
`ORADYN_NO_BLANKSTRIP` | `&H2&` | Do not remove trailing blanks from character string data retrieved from the database.
`ORADYN_READONLY` | `&H4&` | Force dynaset to be read-only.
`ORADYN_NOCACHE` | `&H8&` | Do not create a local dynaset data cache. Without the local cache, previous rows within a dynaset are unavailable; however, increased performance results during retrieval of data from the database (move operations) and from the rows (field operations). Use this option in applications that make single passes through the rows of a dynaset for increased performance and decreased resource usage.
`ORADYN_ORAMODE` | `&H10&` | Same as Oracle Mode for a database except it affects only the dynaset being created. If database was created in Oracle Mode, the dynaset inherits the property from it (for compatibility).
`ORADYN_NO_REFETCH` | `&H20&` | Behaves same as `ORADB_NO_REFETCH` mode for a database except this mode affects only the dynaset being created. If the database was created in `ORADB_NO_REFETCH` mode, the dynaset inherits the property for compatibility.
`ORADYN_N_MOVEFIRST` | `&H40&` | Does not force a `MoveFirst` when the dynaset is created. `BOF` and `EOF` are both true.
`ORADYN_DIRTY_WRITE` | `&H80&` | `Update` and `Delete` methods do not check for read consistency.

These values can be found in the `oraconst.txt` file located in:

`ORACLE_BASE\ORACLE_HOME``\rdbms\oo4o`

Remarks

The SQL statement must be a `SELECT` statement or an error is returned. Features such as simple views and synonyms can be used freely. You can also use schema references, column aliases, table joins, nested select statements, and remote database references, but in each case you end up with a read-only dynaset.

If you use a complex expression or SQL function on a column, such as `"sal + 100"` or `"abs(sal)"` , you get an updatable dynaset, but the column associated with the complex expression is not updatable.

Object names generally are not modifed, but in certain cases, they can be changed. For example, if you use a column alias, you must use the alias to refer to the field by name. If you use spaces in a complex expression, you must refer to the column without the spaces, because the database removes spaces. Note that you can always refer to a field by number, that is, by its ordinal position in the `SELECT` statement.

Executing the SQL `SELECT` statement generates a commit operation to the database by default. To avoid this, use the `BeginTrans` method on the session object before using the `CreateDynaset` method.

The updatability of the resultant dynaset depends on the Oracle SQL rules of updatability, on the access you have been granted, and on the options flag.

Updatability Conditions

For the dynaset to be updatable, three conditions must be met:

- A SQL statement must refer to a simple column list or to the entire column list (*).

- The statement must not set the read-only flag of the options argument.

- Oracle must permit `ROWID` references to the selected rows of the query.

Any SQL statement that does not meet these criteria is processed, but the results are not updatable and the `Updatable` property of the dynaset returns `False`.

This method automatically moves to the first row of the created dynaset.

You can use SQL bind variables in conjunction with the `OraParameters` collection.

Examples

This example demonstrates the `CreateCustomDynaset` method. Copy and paste this code into the definition section of a form, then press F5.

    Sub Form_Load ()

     'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")

     'Create the OraDatabase Object by opening a connection to Oracle.
     Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

     'Create the OraDynaset Object using sliceSize as 256,perblock size as 16, no. of
     'blocks as 20, fetchLimit as 20,FetchSize as 4096

     Set OraDynaset = OraDatabase.CreateCustomDynaset("select empno, " & _
                   "ename from emp", 0&,256,16,20,20,4096)

     'Display the first record.
     MsgBox "Employee " & OraDynaset.Fields("empno").value & ", #" & _
                    OraDynaset.Fields("ename").value

    End Sub

See Also: SnapShot Property

## CreateDatabasePool Method

Applies To

OraSession Object

Description

Creates a pool of `OraDatabase` objects. Only one pool can be created for each `OraSession` object.

Usage

    CreateDatabasePool (long initialSize, long maxSize, long timeoutValue, BSTR database_name, BSTR connect_string, long options)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`initialSize` | The initial size of the pool.
`maxSize` | The maximum size to which the pool can grow.
`timeoutValue` | If an `OraDatabase` object in the pool is idle for the `timeoutValue` value specified, the database connection that it contains is disconnected. The connection is reopened if the pool item is used again. This value is in seconds.
`database_name` | The Oracle network specifier used when connecting the data control to a database.
`connectString` | The user name and password to be used when connecting to an Oracle database.
`options` | A bit flag word used to set the optional modes of the database. If `options` = `0`, the default mode settings apply. "Constants" shows the available modes.

Remarks

The `OpenDatabase` method of the `OraSession` object is used to establish a connection to an Oracle database. This method returns a reference to the `OraDatabase` object which is then used for executing SQL statements and PL/SQL blocks. The connection pool in OO4O is a pool of `OraDatabase` objects. The pool is created by invoking the `CreateDatabasePool` method of the `OraSession` interface.

Exceptions are raised by this call if:

- A pool already exists.

- An error occurs in creating a connection to Oracle Database.

- Invalid values for arguments are passed (that is, `initialSize` > `maxSize`).

The `LastServerErr` property of the `OraSession` object contains the code for the specific cause of the exception resulting from an Oracle Database error.

One possible connection error that could be returned is:

    ORA-28001 "the password has expired"

The user can change the password using the `ChangePassword` method.

See Also: DestroyDatabasePool Method GetDatabaseFromPool Method RemoveFromPool Method ChangePassword (OraSession) Method LastServerErr Property

## CreateDynaset Method

Applies To

OraDatabase Object

Description

Creates an `OraDynaset` object from the specified SQL `SELECT` statement and options.

Usage

    Set oradynaset = oradatabase.CreateDynaset(sql_statement, options, SnapShotID)
    Set oradynaset = oradatabase.DbCreateDynaset(sql_statement, options, SnapShotID)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`sql_statement` | A `String` containing any valid Oracle SQL `SELECT` statement.
`options` | A bit flag indicating the status of any optional states of the dynaset. You can combine one or more options by adding their respective values. Specifying the constant `ORADYN_DEFAULT` or the value `&H0&` gives the following defaults for the dynaset: Behave like Visual Basic Mode for a database: Field values not explicitly set are set to `Null`, overriding database column defaults. Perform automatic binding of database parameters. Remove trailing blanks from character string data retrieved from the database. Create an updatable dynaset. Cache data on client. Force a `MoveFirst` when the dynaset is created. Maintain read-consistency.
`SnapShotID` [optional] | A `ID` of the snapshot obtained from the `SnapShot` property of an `OraDynaset` object.

Constants

The following table lists constants and values for the options flag.

Constant | Value | Description
--- | --- | ---
`ORADYN_DEFAULT` | `&H0&` | Accept the default behavior.
`ORADYN_NO_AUTOBIND` | `&H1&` | Do not perform automatic binding of database parameters.
`ORADYN_NO_BLANKSTRIP` | `&H2&` | Do not remove trailing blanks from character string data retrieved from the database.
`ORADYN_READONLY` | `&H4&` | Force dynaset to be read-only.
`ORADYN_NOCACHE` | `&H8&` | Do not create a local dynaset data cache. Without the local cache, previous rows within a dynaset are unavailable; however, increased performance results during retrieval of data from the database (move operations) and from the rows (field operations). Use this option in applications that make single passes through the rows of a dynaset for increased performance and decreased resource usage.
`ORADYN_ORAMODE` | `&H10&` | Behave the same as Oracle Mode for a database except affect only the dynaset being created. If database was created in Oracle Mode, the dynaset inherits the property from it (for compatibility).
`ORADYN_NO_REFETCH` | `&H20&` | Behave the same as `ORADB_NO_REFETCH` mode for a database except affect only the dynaset being created. If the database was created in `ORADB_NO_REFETCH` mode, the dynaset inherits the property for compatibility.
`ORADYN_NO_MOVEFIRST` | `&H40&` | Does not force a `MoveFirst` when the dynaset is created. `BOF` and `EOF` are both true.
`ORADYN_DIRTY_WRITE` | `&H80&` | `Update` and `Delete` methods do not check for read consistency.

These values can be found in the `oraconst.txt` file.

Remarks

Features such as simple views and synonyms can be used freely. You can also use schema references, column aliases, table joins, nested select statements and remote database references, but in each case, the dynaset is read-only.

If you use a complex expression or SQL function on a column, such as `"sal + 100"` or `"abs(sal)"` , you get an updatable dynaset, but the column associated with the complex expression is not updatable.

Object names generally are not modifed, but in certain cases they can be changed. For example, if you use a column alias, you must use the alias to refer to the field by name. Also, if you use spaces in a complex expression, you must refer to the column without the spaces, since the database strips spaces. Note that you can always refer to a field by number, that is, by its ordinal position in the `SELECT` statement.

Executing the `Update` method generates a commit operation to the database by default. To avoid this, use the `BeginTrans` method on the session object before using the `CreateDynaset` method.

The updatability of the resultant dynaset depends on the Oracle SQL rules of updatability, on the access you have been granted, and on the options flag. For the dynaset to be updatable, these conditions must be met:

- A SQL statement must refer to a simple column list or to the entire column list (*).

- The statement must not set the read-only flag of the options argument.

- Oracle Database must permit `ROWID` references to the selected rows of the query.

Any SQL statement that does not meet these criteria is processed, but the results are not updatable and the `Updatable` property of the dynaset returns `False`. This method automatically moves to the first row of the created dynaset. You can use SQL bind variables in conjunction with the `OraParameters` collection.

The `SnapShotID` option causes a snapshot descriptor to be created for the `SQLStmt` object created. This property can later be obtained and used in creation of other `SQLStmt` or `OraDynaset` objects. Execution snapshots provide the ability to ensure that multiple commands executed in the context of multiple `OraDatabase` objects operate on the same consistent snapshot of the committed data in the database.

Examples

This example demonstrates `CreateObject`, `OpenDatabase` and `CreateDynaset` methods. Copy and paste this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

     'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")

     'Create the OraDatabase Object by opening a connection to Oracle.
     Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

     'Create the OraDynaset Object.
     Set OraDynaset = OraDatabase.CreateDynaset("select empno, ename from emp", 0&)

     'Display the first record.
     MsgBox "Employee " & OraDynaset.Fields("empno").value & ", #" & _
                    OraDynaset.Fields("ename").value

    End Sub

See Also: Clone Method MoveFirst, MoveLast, MoveNext, and MovePrevious Methods MovePreviousn, MoveNextn, MoveRel, and MoveTo Methods OpenDatabase Method Updatable Property OraDynaset Object OraParameter Object OraParameters Collection Update Method BeginTrans Method SnapShot Property

## CreateIterator Method

Applies To

OraCollection Object

Description

Creates an iterator to scan the elements of a collection.

Usage

    OraCollection.CreateIterator

Remarks

This method creates an iterator for scanning the elements of an Oracle collection. Accessing collection elements using the iterator is faster than using an index on the instance of a collection.

Examples

Example: OraCollection Iterator

The following example illustrates the use of an Oracle collection iterator.

Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in OraCollection Examples" .

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim OraDynaset As OraDynaset
    Dim CourseList As OraCollection
    Dim Course As OraObject

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", scott/tiger", 0&)

    'Create a dynaset object from division
    Set OraDynaset = OraDatabase.CreateDynaset("select courses from" & _
                     "division where name='History'", 0&)

    'Retrieve a Courses column from Division.
    Set CourseList = OraDynaset.Fields("Courses").Value

    'Create the iterator
    CourseList.CreateIterator

    'Initialize the iterator to point to the beginning of a collection
    CourseList.InitIterator

    'Call IterNext to read CourseList until the end
    While CourseList.EOC = False
        Set Course = CourseList.ElementValue
        course_no = Course.course_no
        Title = Course.Title
        Credits = Course.Credits
        CourseList.IterNext
    Wend

    'Call IterPrev to read CourseList until the beginning
    CourseList.IterPrev

    While CourseList.BOC = False
        Set Course = CourseList.ElementValue
        course_no = Course.course_no
        Title = Course.Title
        Credits = Course.Credits
        CourseList.IterPrev
    Wend

See Also: DeleteIterator Method InitIterator Method

## CreateNamedSession Method

Applies To

OraSession Object

Description

Creates and returns a new named `OraSession` object.

Usage

    orasession = orasession.CreateNamedSession(session_name)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`session_name` | A `String` specifying the name of the session.

Remarks

Using this method, you can create named sessions that can be referenced later in the same application as long as the session object referred to is in scope. Once a session has been created, the application can reference it by way of the `ConnectSession` method or the `OraSessions` collection of their respective `OraClient` object. The `OraSessions` collection only contains sessions created within the current application. Therefore, it is not possible to share sessions across applications, only within applications.

This method is provided for simplicity and is equivalent to the `CreateSession` method of the `OraClient` object.

Examples

This example demonstrates the use of `ConnectSession` and `CreateNamedSession` methods to allow an application to use a session it previously created, but did not save. Copy this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

     'Declare variables
     Dim dfltsess As OraSession
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset

     'Create the default OraSession Object.
     Set dfltsess = CreateObject("OracleInProcServer.XOraSession")

     'Try to connect to "ExampleSession". If it does not exist
     'an error is generated.
     On Error GoTo SetName
     Set OraSession = dfltsess.ConnectSession("ExampleSession")
     On Error GoTo 0

    'Create the OraDatabase Object by opening a connection to Oracle.
     Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

     'Create the OraDynaset Object.
     Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)

     'Display or manipulate data here

    Exit Sub

    SetName:
    'The session named "ExampleSession" was not found, so create it.
    Set OraSession = dfltsess.CreateNamedSession("ExampleSession")
    Resume Next

    End Sub

See Also: CreateSession Method ConnectSession Method OraClient Object OraSessions Collection

## CreateOraIntervalDS Method

Applies To

OraSession Object

Description

Creates the `OraIntervalDS` object. This `OraIntervalDS` represents an Oracle `INTERVAL` `DAY` `TO` `SECOND` data type.

Usage

    Set OraIntervalDSObj = OraSession.CreateOraIntervalDS value

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, a numeric value, an `OraIntervalDS`, or an `OraNumber` object.

Return Values

OraIntervalDS Object

Remarks

An `OraSession` object must be created before an `OraIntervalDS` object can be created.

If `value` is a `Variant` of type `String`, it must be in the following format: [+/-] Day HH:MI:SSxFF.

If `value` is a numeric value, the value provided should represent the total number of days that the constructed `OraIntervalDS` represents.

A `Variant` of type `OraIntervalDS` can also be passed. A cloned `OraIntervalDS` is returned.

Examples

    Dim oraIDS as OraIntervalDS
    Dim oraIDS2 as OraIntervalDS
    Dim oraNum  as OraNumber

    'Create an OraIntervalDS using a string which represents 1 days, 2 hours,
    '3 minutes, 4 seconds and 500000 nanoseconds
    Set oraIDS = oo4oSession.CreateOraIntervalDS("1 2:3:4.005")

    'Create an OraIntervalDS using a numeric value which represents
    '1 days and 12 hours
    Set oraIDS = oo4oSession.CreateOraIntervalDS(1.5)

    'Create an OraIntervalDS using an OraIntervalDS
    Set oraIDS2 = oo4oSession.CreateOraIntervalDS(oraIDS)

See Also: OraNumber Object OraIntervalDS Object

## CreateOraIntervalYM Method

Applies To

OraSession Object

Description

Creates the `OraIntervalYM` object. This `OraIntervalYM` represents an Oracle `INTERVAL` `YEAR` `TO` `MONTH` data type.

Usage

    Set OraIntervalYMObj = OraSession.CreateOraIntervalYM value

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, a numeric value, or an `OraIntervalYM` object.

Return Values

OraIntervalYM Object

Remarks

An `OraSession` object must be created before an `OraIntervalYM` object can be created.

If `value` is a `Variant` of type `String`, it must be in the following format: [+/-] YEARS-MONTHS.

If `value` is a numeric value, the value provided should represent the total number of years that the constructed `OraIntervalYM` object represents.

A `Variant` of type `OraIntervalYM` can also be passed. A cloned `OraIntervalYM` object is returned.

Examples

    Dim oraIYM as OraIntervalYM
    Dim oraIYM2 as OraIntervalYM

    'Create an OraIntervalYM using a string which represents 1 year and 2 months
    Set oraIYM = oo4oSession.CreateOraIntervalYM("1- 2")

    'Create an OraIntervalYM using a numeric value which represents
    '1 year and 6 months
    Set oraIYM = oo4oSession.CreateOraIntervalYM(1.5)

    'Create an OraIntervalYM using an OraIntervalYM
    Set oraIYM2 = oo4oSession.CreateOraIntervalYM(oraIYM)

See Also: OraIntervalYM Object OraNumber Object

## CreateOraNumber Method

Applies To

OraSession Object

Description

Creates an `OraNumber` object. This `OraNumber` represents an Oracle `NUMBER` data type.

Usage

    OraNumber = OraSession.CreateOraNumber(inital_value, format)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`initial_value` | Initial value of `OraNumber`. A `Variant` of type `OraNumber`, string or a numeric value.
`format` [optional] | Format string to be used when displaying `OraNumber` value.

Return Value

OraNumber Object

Remarks

For more information about format strings, see the format property on the `OraNumber` object.

See Also: ConnectSession Method CreateSession Method OraClient Object OraNumber Object OraSessions Collection

## CreateOraObject (OraDatabase) Method

Applies To

OraDatabase Object

Description

Creates a value instance or referenceable object in the cache and returns the associated OO4O object.

Usage

    OraObject1 = OraDatabase.CreateOraObject(schema_name)
    OraRef1 = OraDatabase.CreateOraObject(schema_name,table_name)
    OraCollection1 = OraDatabase.CreateOraObject(schema_name)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`OraObject1` | A valid `OraObject` object representing a newly created value instance.
`OraRef1` | A valid `OraRef` object representing a newly created referenceable object.
`OraCollection` | A valid `OraCollection` object representing a newly created collection instance.
`schema_name` | A `String` specifying the schema name of the value instance to be created.
`table_name` | A `String` specifying the table name of the referenceable object to be created.

Remarks

If the `table_name` argument is not specified, it creates a value instance in the client and returns an `OraObject` or `OraCollection` object. If the `table_name` argument is specified, it creates a referenceable object in the database and returns an associated `OraRef` object.

Examples

`OraObject` and `OraRef` object examples are provided. Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in the OraObject and OraRef Examples".

Example: Creating an OraObject Object

The following example illustrates the use of the `CreateOraObject` method to insert a value instance. The row containing `ADDRESS` is inserted as a value instance in the database.

Dynaset Example

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim AddressNew as OraObject

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", scott/tiger", 0&)

    'create a dynaset object from person_tab
    set OraDynaset = OraDatabase.CreateDynaset("select * from person_tab", 0&)

    ' create a new Address object in OO4O
    set AddressNew = OraDatabase.CreateOraObject("ADDRESS")

    'initialize the Address object attribute to new value
    AddressNew.Street = "Oracle Parkway"
    AddressNew.State = "CA"

    'start the dynaset AddNew operation and
    'set the Address field to new address value
    OraDynaset.Addnew
    OraDynaset.Fields("ADDR").Value = AddressNew
    OraDynaset.Update

OraParameter Example

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim AddressNew as OraObject

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create an  OraParameter object represent Address object bind Variable
    OraDatabase.Parameters.Add "ADDRESS", Null, ORAPARM_INPUT, _
                 ORATYPE_OBJECT, "ADDRESS"

    ' create a new Address object in OO4O
    set AddressNew = OraDatabase.CreateOraObject("ADDRESS")

    'initialize the Address object attribute to new value
    AddressNew.Street = "Oracle Parkway"
    AddressNew.State = "CA"

    'set the Address to ADDRESS parameter
    Oradatabase.Parameters("ADDRESS").Value = AddressNew

    'execute the sql statement which updates Address in the person_tab
    OraDatabase.ExecuteSQL ("insert into person_tab values ('Eric',30,:ADDRESS)")

Example: Creating an OraRef Object

The following example illustrates the use of the `CreateOraObject` method to insert referenceable objects.

In this example, a new `PERSON` is inserted as a referenceable object in the database.

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim Person  as OraRef

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'CreteOraObject   creates a new referenceable
    'object in the PERSON_TAB object table and returns associated OraRef
    set Person = OraDatabase.CreateOraObject("PERSON","PERSON_TAB")

    'modify the attributes of Person
    Person.Name = "Eric"

    Person.Age = 35
    'Update method inserts modified referenceable object in the PERSON_TAB.
    Person.Update

## CreateOraTimeStamp Method

Applies To

OraSession Object

Description

Creates a new `OraTimeStamp` object. This `OraTimeStamp` method represents an Oracle `TIMESTAMP` or an Oracle `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE` data type.

Usage

    Set OraTimeStampObj = OraSession.CreateOraTimeStamp value format

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, `Date`, or `OraTimeStamp`.
[`in`] [optional] `format` | `TimeStamp` format string to be used when displaying or interpreting an `OraTimeStamp` object as a string. If format is not specified, the `TimeStamp` string is interpreted using the session `TIMESTAMP` format (`NLS_TIMESTAMP_FORMAT` format).

Return Values

OraTimeStamp Object

Remarks

An `OraSession` object must created before an `OraTimeStamp` object can be created.

If `value` is a `Variant` of type `String`, the string format must match the datetime format specified in the `format` argument. If `format` is not specified, the string format must match the session `TIMESTAMP` format (`NLS_TIMESTAMP_FORMAT`).

If `format` is specified, it is stored in the `Format` property of the `OraTimeStamp` ; otherwise, the session `TIMESTAMP` format is stored in the `OraTimeStamp` `Format` property.

Examples

    Dim oraTS as OraTimeStamp
    Dim oraTS1 as OraTimeStamp
    Dim date as Date

    'Create an OraTimeStamp using a string assuming the session
    'TIMESTAMP format is "DD-MON-RR HH.MI.SSXFF AM"
    Set oraTS = oo4oSession.CreateOraTimeStamp("12-JAN-2003 12.0.0.0 PM")

    'Create an OraTimeStamp using a string and a format
    Set oraTS = oo4oSession.CreateOraTimeStamp("2003-01-12 12:00:00 PM", _
             "YYYY-MM-DD HH:MI:SS AM")

    'Create an OraTimeStamp using a Date
    date = #1/12/2003#

    Set oraTS = oo4oSession.CreateOraTimeStamp(date)

    'Create an OraTimeStamp  using an OraTimeStamp
    Set oraTS1 = oo4oSession.CreateOraTimeStamp(oraTS)

See Also: OraTimeStamp Object OraNumber Object

## CreateOraTimeStampTZ Method

Applies To

OraSession Object

Description

Creates a new `OraTimeStampTZ` object. This `OraTimeStampTZ` object represents an Oracle `TIMESTAMP` `WITH` `TIME` `ZONE` data type.

Usage

    Set OraTimeStampTZObj = OraSession.CreateOraTimeStampTZ value format

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, `Date`, or `OraTimeStampTZ`.
[[`in`] [optional] `format` | `TIMESTAMP` `WITH` `TIME` `ZONE` format string to be used when displaying or interpreting an `OraTimeStampTZ` object as a string. If format is not specified, the `TIMESTAMP` `WITH` `TIME` `ZONE` string is interpreted using the session `TIMESTAMP` `WITH` `TIME` `ZONE` format (`NLS_TIMESTAMP_TZ_FORMAT` format).

Return Values

OraTimeStampTZ Object

Remarks

An `OraSession` object must be created before an `OraTimeStampTZ` object can be created.

If `value` is a `Variant` of type `String`, the string format must match the datetime format specified in the format argument if format is specified; otherwise, the string format must match the session `TIMESTAMP` `WITH` `TIME` `ZONE` format (`NLS_TIMESTAMP_TZ_FORMAT`).

If `value` is a `Variant` of type `Date`, the date-time value in the `Date` is interpreted as the date-time value in the time zone of the session. The `TimeZone` property in the `OraTimeStampTZ` object contains the time zone of the session.

If `format` is specified, it is stored in the `Format` property of the `OraTimeStampTZ` object, otherwise the session `TIMESTAMP` `WITH` `TIME` `ZONE` format is stored in the `Format` property of `OraTimeStampTZ` object.

Examples

    Dim oraTSZ as OraTimeStampTZ
    Dim oraTSZ1 as OraTimeStampTZ
    Dim date as Date

    'Create an OraTimeStampTZ using a string assuming the session
    'TIMESTAMP WITH TIME ZONE format is "DD-MON-RR HH.MI.SSXFF AM TZH:TZM"
    Set oraTSZ = oo4oSession.CreateOraTimeStampTZ( "12-JAN-2003" & _
                 "12.0.0.0 PM -03:00")

    'Create an OraTimeStampTZ using a string and a format
    Set oraTSZ = oo4oSession.CreateOraTimeStampTZ( "2003-01-12" & _
                 "12:00:00 PM -03:00", "YYYY-MM-DD HH:MI:SS AM TZH:TZM")

    'Create an OraTimeStampTZ using a Date
    date = #1/12/2003#
    Set oraTSZ = oo4oSession.CreateOraTimeStampTZ(date)

    'Create an OraTimeStampTZ  using an OraTimeStampTZ
    Set oraTSZ1 = oo4oSession.CreateOraTimeStampTZ(oraTSZ)

See Also: OraTimeStampTZ Object OraNumber Object

## CreatePLSQLCustomDynaset Method

Applies To

OraDatabase Object

Deprecated.

For information on how to perform these tasks, see "Returning PL/SQL Cursor Variables".

Description

Creates a dynaset from a PL/SQL cursor using custom cache and fetch parameters. The SQL statement should be a stored procedure or anonymous block. The resulting dynaset is read-only. Attempting to set the SQL property results in an error. The dynaset can be refreshed with new parameters.

Usage

    set OraDynaset = CreatePlsqlCustomDynaset(SQLStatement, CursorName, options, slicesize, perblock, blocks, FetchLimit, FetchSize)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`SQLStatement` | Any valid Oracle PL/SQL stored procedure or anonymous block.
`CursorName` | Name of the cursor created in the PL/SQL stored procedure.
`options` | A bit flag indicating the status of any optional states of the dynaset. You can combine one or more options by adding their respective values.
`slicesize` | Cache slice size.
`perblock` | Cache slices for each block.
`blocks` | Cache maximum number of blocks.
`FetchLimit` | Fetch array size.
`FetchSize` | Fetch array buffer size.

Constants

The options flag values are:

Constant | Value | Description
--- | --- | ---
`ORADYN_DEFAULT` | `&H0&` | Accept the default behavior.
`ORADYN_NO_AUTOBIND` | `&H1&` | Do not perform automatic binding of database parameters.
`ORADYN_NO_BLANKSTRIP` | `&H2&` | Do not remove trailing blanks from character string data retrieved from the database.
`ORADYN_NOCACHE` | `&H8&` | Do not create a local dynaset data cache. Without the local cache, previous rows within a dynaset are unavailable; however, increased performance results during retrieval of data from the database (move operations) and from the rows (field operations). Use this option in applications that make single passes through the rows of a dynaset for increased performance and decreased resource use.
`ORADYN_NO_MOVEFIRST` | `&H40&` | Do not force a `MoveFirst` when the dynaset is created. `BOF` and `EOF` are both true.

These values can be found in the `oraconst.txt` file.

Remarks

The SQL statement must be a PL/SQL stored procedure with `BEGIN` and `END` around the call, as if it were executed as an anonymous PL/SQL block; otherwise, an error is returned. The `CursorName` argument should exactly match the cursor created inside the stored procedure or anonymous PL/SQL block; otherwise an error is returned. The cursor created inside the stored procedure should represent a valid SQL `SELECT` statement.

You do not need to bind the PL/SQL cursor variable using the `OraParameters` `Add` method if the stored procedure returns a cursor as an output parameter. You can still use PL/SQL bind variables in conjunction with the `OraParameters` collection.

This method automatically moves to the first row of the created dynaset.

Specifying `ORADYN_READONLY`, `ORADYN_ORAMODE`, `ORADYN_NO_REFETCH`, `ORADYN_DIRTY_WRITE` options have no effect on the dynaset creation.

See Also: OraParameters Collection Add Method

## CreatePLSQLDynaset Method

Applies To

OraDatabase Object

Deprecated.

For information on how to perform these tasks, see "Returning PL/SQL Cursor Variables".

Description

Creates a dynaset from a PL/SQL cursor. The SQL statement should be a stored procedure or an anonymous block. The resulting dynaset is read-only and attempting to set SQL property results in an error. Dynasets can be refreshed with new parameters similar to dynasets without cursors.

Usage

    set OraDynaset = CreatePLSQLDynaset(SQLStatement, CursorName, options)

Arguments

Arguments | Description
--- | ---
`SQLStatement` | Any valid Oracle PL/SQL stored procedure or anonymous block.
`CursorName` | Name of the cursor created in the PL/SQL stored procedure.
`options` | A bit flag indicating the status of any optional states of the dynaset. You can combine one or more options by adding their respective values.

Constants

The options flag values are:

Constant | Value | Description
--- | --- | ---
`ORADYN_DEFAULT` | `&H0&` | Accept the default behavior.
`ORADYN_NO_BLANKSTRIP` | `&H2&` | Do not remove trailing blanks from character string data retrieved from the database.
`ORADYN_NOCACHE` | `&H8&` | Do not create a local dynaset data cache. Without the local cache, previous rows within a dynaset are unavailable; however, increased performance results during retrieval of data from the database (move operations) and from the rows (field operations). Use this option in applications that make single passes through the rows of a dynaset for increased performance and decreased resource usage.
`ORADYN_NO_MOVEFIRST` | `&H40&` | Do not force a `MoveFirst` operation when the dynaset is created. `BOF` and `EOF` are both true.

These values can be found in the `oraconst.txt` file.

Remarks

The SQL statement must be a PL/SQL stored procedure with `BEGIN` and `END` statements around the call, as if it were executed as an anonymous PL/SQL block; otherwise an error is returned. The `CursorName` argument should exactly match the cursor created inside the stored procedure or anonymous PL/SQL block; otherwise, an error is returned. Cursors created inside the stored procedure should represent a valid SQL `SELECT` statement.

You do not need to bind the PL/SQL cursor variable using the OraParameters.Add method if the stored procedure returns a cursor as a output parameter. You can still use PL/SQL bind variables in conjunction with the `OraParameters` collection.

This method automatically moves to the first row of the created dynaset.

Specifying the `ORADYN_READONLY`, `ORADYN_ORAMODE`, `ORADYN_NO_REFETCH`, or `ORADYN_DIRTY_WRITE` options have no effect on the dynaset creation.

Examples

This example demonstrates the use of PL/SQL cursor in the `CreatePlsqlDynaset` method and `Refresh` method. This example returns a PL/SQL cursor as a dynaset for the different values of the `DEPTNO` parameter. Make sure that corresponding stored procedure (found in `EMPCUR.SQL`) is available in the Oracle database. and paste this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

     'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase

     Dim OraDynaset As OraDynaset

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")

     'Create the OraDatabase Object by opening a connection to Oracle.
     Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    ' Create the Deptno parameter
     OraDatabase.Parameters.Add "DEPTNO", 10, ORAPARM_INPUT
     OraDatabase.Parameters("DEPTNO").ServerType = ORATYPE_NUMBER

    ' Create OraDynaset based on "EmpCursor" created in stored procedure.
     Set OraDynaset = OraDatabase.CreatePLSQLDynaset("Begin Employee.GetEmpData" & _
                   "(:DEPTNO,:EmpCursor); end;", "EmpCursor", 0&)

     'Should display KING
     MsgBox OraDynaset.Fields("ENAME").Value

     'Should display 7839
     MsgBox OraDynaset.Fields("EMPNO").Value

     ' Now set the deptno value to 20
     OraDatabase.Parameters("DEPTNO").Value = 20

     'Refresh the dynaset
     OraDynaset.Refresh

     'Should display JONES
     MsgBox OraDynaset.Fields("ENAME").Value

     'Should display 7566
     MsgBox OraDynaset.Fields("EMPNO").Value

      'Remove the parameter.
     OraDatabase.Parameters.Remove ("DEPTNO")

     End Sub

## CreateSession Method

Applies To

OraClient Object

Description

Creates a new named `OraSession` object.

Usage

    orasession = oraclient.CreateSession(session_name)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`session_name` | A `String` specifying the name of the session.

Remarks

Use this method to create named sessions that can be referenced later in the same application without having to explicitly save the `OraSession` object when it is created. Once a session has been created, the application can reference it by way of the `ConnectSession` method or the `OraSessions` collection of their respective `OraClient` object. The `OraSessions` collection only contains sessions created within the current application. This means that it is not possible to share sessions across applications, only within applications.

Examples

This example demonstrates how to create a session object using the `CreateSession` method of the client object. Copy and paste this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

     'Declare variables
     Dim OraClient As OraClient
     Dim OraSession As OraSession
     Dim NamedOraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")

     'Get the OraClient object.
     Set OraClient = OraSession.Client

     'Create a named OraSession Object
     'Alternatively, you could use the CreateNamedSession
     'method of the OraSession Object.

     Set NamedOraSession = OraClient.CreateSession("ExampleSession")

     'Create the OraDatabase Object by opening a connection to Oracle.
     Set OraDatabase = NamedOraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

     'Create the OraDynaset Object.
     Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)

    End Sub

See Also: OraSession Object

## CreateSQL Method

Applies To

OraDatabase Object

Description

Executes the SQL statement and creates an `OraSQLStmt` object from the specified SQL statement and options.

Usage

    Set orasqlstmt = oradatabase.CreateSQL(sql_statement, options)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`sql_statement` | Any valid Oracle SQL statement.
`options` | A bit flag indicating the status of any optional states of the `OraSQLStmt` object. You can combine one or more options by adding their respective values.

Constants

The options flag values are:

Constant | Value | Description
--- | --- | ---
`ORASQL_NO_AUTOBIND` | `&H1&` | Do not perform automatic binding of database parameters.
`ORASQL_FAILEXEC` | `&H2&` | Raise error and do not create SQL statement object.
`ORASQL_NONBLK` | `&H4&` | Execute SQL in a nonblocking state.

These values can be found in the `oraconst.txt` file.

Remarks

The SQL statement can be one continuous line with no breaks. If it is necessary to break the line, be sure to use line feeds (ASCII 10). Do not use carriage returns (ASCII 13), because the underlying Oracle Database functions treat carriage returns as null terminators.

You can use PL/SQL bind variables in conjunction with the `OraParameters` collection.

Executing the SQL statement generates a commit to the database by default. To avoid this, use the `BeginTrans` method on the session object before using the `CreateSQL` method.

When executing PL/SQL blocks or calling stored procedures, you must include a `BEGIN` and `END` statement around your call as if you were executing an anonymous PL/SQL block. This is equivalent to the `EXECUTE` command of SQL*Plus and SQL*DBA.

If the `ORASQL_FAILEXEC` option is used, an error is raised during `SQLstmt` object creation failure (on `SQLstmt` object refresh). The `SQLstmt` object is not created and cannot be refreshed.

Note: Use the `CreateSQL` method with care, because any SQL statement or PL/SQL block that is executed might cause errors afterward when you use the `Edit` method on open dynasets.

Data Type

`String`

Examples

This example demonstrates the use of parameters, the `CreateSQL` method, the `Refresh` method, and the SQL property for `OraSQLStmt` object. Copy and paste this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

     'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraSqlStmt As OraSQLStmt

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")

     'Create the OraDatabase Object by opening a connection to Oracle.
     Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

     OraDatabase.Parameters.Add "EMPNO", 7369, 1
     OraDatabase.Parameters("EMPNO").ServerType = 2  'ORATYPE_NUMBER

     OraDatabase.Parameters.Add "ENAME", 0, 2
     OraDatabase.Parameters("ENAME").ServerType = 1  'ORATYPE_VARCHAR2

     Set OraSqlStmt = OraDatabase.CreateSQL("Begin Employee.GetEmpName" & _
              "(:EMPNO, :ENAME); end;", 0&)

     'Notice that the SQL statement is NOT modified.
     MsgBox OraSqlStmt.SQL

     'Should display SMITH
     MsgBox OraDatabase.Parameters("ENAME").Value

     'Change the value of the empno parameter.
     OraDatabase.Parameters("EMPNO").Value = 7499

     'Refresh the sqlstmt
     OraSqlStmt.Refresh

     'Should display ALLEN
     MsgBox OraDatabase.Parameters("ENAME").Value

     'Notice that the SQL statement is NOT modified.
     MsgBox OraSqlStmt.SQL

     'Remove the parameter.
     OraDatabase.Parameters.Remove ("job")

     End Sub

See Also: "Asynchronous Processing" for more information about the `ORASQL_NONBLK` option BeginTrans Method OraSQLStmt Object CreateSQL Method ExecuteSQL Method Refresh Method

## CreateTempBLOB/CLOB Method

Applies To

OraDatabase Object

Description

Creates a temporary LOB in the database.

Usage

    Set OraBLOB = OraDatabase.CreateTempBLOB(use_caching) Set OraCLOB = OraDatabase.CreateTempCLOB(use_caching)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`use_caching` | A boolean value that specifies whether Oracle Database uses caching when accessing this LOB. The default value is `False`.

Remarks

Temporary LOBs are LOBs that do not exist permanently in the database. OO4O programmers commonly use temporary LOBs to pass into stored procedures and functions that have LOB arguments.

Temporary LOBs do not require or take part in transactions. (It is not necessary to acquire a lock before write operations, and rollbacks have no effect on temporary LOBs.)

The `use_caching` argument directs Oracle to use caching when accessing the temporary LOB. This is suggested when multiple accesses are expected on a single LOB. Caching is not required for the typical case, where a LOB is created, filled with data, passed to a stored procedure, and then discarded.

Temporary LOBs exist on the database until no more references to the corresponding `OraBLOB` or `OraCLOB` exist on the client. Note that these references include any `OraParameter` or `OraParamArray` that contain a temporary `OraBLOB` or `OraCLOB` object.

Examples

Example: Passing a Temporary CLOB to a Stored Procedure

The following example illustrates the use of the `CreateTempClob` method to create a `OraCLOB`. The `OraCLOB` is then populated with data and passed to a stored procedure which has an argument of type `CLOB`.

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraClob as OraClob

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.

    Set OraDatabase = OraSession.OpenDatabase("ExampleDb","scott/tiger", 0&)

    'Create the stored procedure used in this example
    OraDatabase.ExecuteSQL ("create or replace procedure GetClobSize" & _
               "(in_clob IN CLOB, clobsize OUT NUMBER) as Begin clobsize" & _
               " := DBMS_LOB.GETLENGTH(in_clob); End;")

    'create an OraParameter object to represent Clob bind Variable
    OraDatabase.Parameters.Add "CLOB", Null, ORAPARM_INPUT,  ORATYPE_CLOB

    'the size will go into this bind variable
    OraDatabase.Parameters.Add "CLOBSIZE", Null, ORAPARM_OUTPUT,  ORATYPE_NUMBER

    ' create a temporary CLOB
    set OraClob = OraDatabase.CreateTempClob

    'Populate the OraClob with some data. Note that no row locks are needed.
    OraClob.Write "This is some test data"

    'set the Parameter Value to the temporary Lob
    OraDatabase.Parameters("CLOB").Value = OraClob

    'execute the sql statement which updates Address in the person_tab
    OraDatabase.ExecuteSQL ("Begin GetClobSize(:CLOB, :CLOBSIZE); end;")

    'Display the size
    MsgBox OraDatabase.Parameters("CLOBSize").Value

    'these two lines force the temporary clob to be freed immediately
    OraDatabase.Parameters.Remove "CLOB"
    Set OraClob = nothing

## Delete Method

Applies To

OraDynaset Object

Description

Deletes the current row of the specified dynaset.

Usage

    oradynaset.Delete
    oradynaset.DbDelete

Remarks

A row must be current before you can use the `Delete` method; otherwise, an error occurs.

Note that after you call the `Delete` method on a given row in a dynaset in a global transaction (that is, once you issue a `BeginTrans` method), locks remain on the selected rows until you call a `CommitTrans` or `Rollback` method.

Any references to the deleted row produce an error. The deleted row, as well as the next and previous rows, remain current until database movement occurs (using the `MoveFirst`, `MovePrevious`, `MoveNext`, or `MoveLast` methods). Once movement occurs, you cannot make the deleted row current again.

You cannot restore deleted records except by using transactions.

Note: A call to an `Edit`, `AddNew`, or `Delete` method, cancels any outstanding `Edit` or `AddNew` calls before proceeding. Any outstanding changes not saved using an `Update` method are lost during the cancellation.

Examples

This example demonstrates the use of the `Delete` method to remove records from a database. Copy and paste this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

    'Declare variables
    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim OraDynaset As OraDynaset

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("exampledb", "scott/tiger", 0&)

    'Create the OraDynaset Object. Only select the employees in Department 10.
    Set OraDynaset = OraDatabase.CreateDynaset("select * from emp where" & _
              "deptno=10", 0&)

     Do Until OraDynaset.EOF
       OraDynaset.Delete
       OraDynaset.MoveNext
     Loop
     MsgBox "All employees from department 10 removed."

    End Sub

See Also: AddNew Method BeginTrans Method CommitTrans Method Edit Method MoveFirst, MoveLast, MoveNext, and MovePrevious Methods ResetTrans Method Rollback Method

## Delete (OraCollection) Method

Applies To

OraCollection Object

Description

Deletes an element at given index. This method is available only in an `OraCollection` of type `ORATYPE_TABLE` (nested table).

Usage

    OraCollection.Delete index

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `index` | An `Integer` specifying the index of the element to be deleted.

Remarks

The `Delete` method creates holes in the client-side nested table. This method returns an error if the element at the given index has already been deleted or if the given index is not valid for the given table.

Examples

The following example illustrates the `Delete` method. Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in OraCollection Examples" .

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim CourseList as OraCollection

    'create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create a dynaset object from division
    set OraDynaset = OraDatabase.CreateDynaset("select * from division", 0&)

    'retrieve a Courses column from Division.
    'Here Value property of OraField object returns CourseList OraCollection
    set CourseList = OraDynaset.Fields("Courses").Value

    'Delete the CourseList  NestedTable at index 2.
    'Before that lock should be obtained
    OraDynaset.Edit
    CourseList.Delete 2

    OraDynaset.Update

See Also: Type (OraCollection) Property

## Delete (OraRef) Method

Applies To

OraRef Object

Description

Deletes a referenceable object in the database.

Usage

    OraRef.Delete

Remarks

Accessing attributes on the deleted instance results in an error.

Examples

The following example illustrates the `Delete` method. Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in the OraObject and OraRef Examples".

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim Person  as OraRef

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create an  OraParameter object represent Person object bind Variable
    OraDatabase.Parameters.Add "PERSON", Null, ORAPARM_OUTPUT, ORATYPE_REF,"PERSON"

    'execute the sql statement which selects person
    'from the customers table for account = 10
    OraDatabase.ExecuteSQL ("BEGIN select aperson into :PERSON from customers" & _
                       "where account = 10;  END;")

    'get the Person object from OraParameter
    set Person = OraDatabase.Parameters("PERSON").Value

    'delete the Person object in the server for modifying its attributes
    Person.Delete

## DeleteIterator Method

Applies To

OraCollection Object

Description

Deletes a collection iterator.

Usage

    OraCollection.DeleteIterator

Remarks

None.

Examples

See "Example: OraCollection Iterator"

See Also: "CreateIterator Method"

## Dequeue (OraAQ) Method

Applies To

OraAQ Object

Description

Dequeues a message.

Usage

    Q.Dequeue()

Remarks

The message attributes can be accessed with the `OraAQMsg` interface contained in this object. On success, this method returns the message identifier as an array of bytes. Otherwise, it returns an empty array (null).

Examples

Note: The following code sample are models for dequeuing messages. A complete AQ sample can be found in the `\OO4O\VB\SAMPLES \AQ` directory.

Example: Dequeuing Messages of RAW Type

    'Dequeue the first message available
    Q.Dequeue
    Set Msg = Q.QMsg

    'Display the message content
    MsgBox Msg.Value

    'Dequeue the first message available without removing it
    ' from the queue
    Q.DequeueMode = ORAAQ_DQ_BROWSE

    'Dequeue the first message with the correlation identifier
    ' equal to "RELATIVE_MSG_ID"
    Q.Navigation = ORAAQ_DQ_FIRST_MSG
    Q.correlate = "RELATIVE_MESSAGE_ID"
    Q.Dequeue

    'Dequeue the next message with the correlation identifier

    ' of "RELATIVE_MSG_ID"
    Q.Navigation = ORAAQ_DQ_NEXT_MSG
    Q.Dequeue

    'Dequeue the first high priority message
    Msg.Priority = ORAQMSG_HIGH_PRIORITY
    Q.Dequeue

    'Dequeue the message enqueued with message id of Msgid_1
    Q.DequeueMsgid = Msgid_1
    Q.Dequeue

    'Dequeue the message meant for the consumer "ANDY"
    Q.consumer = "ANDY"
    Q.Dequeue

    'Return immediately if there is no message on the queue
    Q.wait = ORAAQ_DQ_NOWAIT
    Q.Dequeue

Example: Dequeuing Messages of Oracle Object Types

    Set OraObj = DB.CreateOraObject("MESSAGE_TYPE")
    Set QMsg = Q.AQMsg(23, "MESSAGE_TYPE","SCOTT")

    'Dequeue the first message available without removing it
    Q.Dequeue
    OraObj = QMsg.Value

    'Display the subject and data
    MsgBox OraObj("subject").Value & OraObj("Data").Value

## Describe Method

Applies To

OraDatabase Object

Description

Describes a schema object. This method returns an instance of the `OraMetaData` interface.

Usage

    OraMetaDataObj = OraDatabase.Describe(SchemaObjectName)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `SchemaObjectName` | A `String` representing the name of the schema object to be described.

Remarks

The following schema object types can be described:

- `Tables`

- `Views`

- `Procedures`

- `Functions`

- `Packages`

- `Sequences`

- `Collections` (`VARRAY`s or nested tables)

- `Types`

Describing any other schema object (for example, a column) or an invalid schema object name raises an error. You should navigate to schema objects not listed here, rather than describing them directly.

This method takes the name of a schema object, such as `emp`, and returns a COM Automation object (`OraMetaData`). The `OraMetaData` object provides methods for dynamically navigating and accessing all the attributes (`OraMDAttribute` collection) of a schema object described.

Examples

Simple Describe Example

The following Visual Basic code illustrates a how to use the `Describe` method to retrieve and display several attributes of the `emp` table.

    Set emp = OraDatabase.Describe("emp")

    'Display the name of the Tablespace
    MsgBox emp!tablespace
    'Display name and data type of each column in the emp table.
    Set empColumns = emp!ColumnList
    Set ColumnList = empColumns.Value

    for i = 0 to ColumnList.Count - 1
      Set Column = ColumnList(i).Value
      MsgBox "Column: " & Column!Name & " Data Type: " & Column!Data Type
    Next i

Describing a Table Example

Before running the following example, make sure that you have the necessary datatypes and tables in the database. See "Schema Objects Used in OraMetaData Examples".

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim OraDynaset As OraDynaset
    Dim OraMetaData As OraMetaData
    Dim OraMDAttribute As OraMDAttribute
    Dim ColumnList As OraMetaData
    Dim Column As OraMetaData

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDB", "scott/tiger", 0&)

    'Use Describe to retrieve the metadata object
    Set OraMetaData = OraDatabase.Describe("EMP")

    'Display the type of the metadata
    MsgBox TypeofMetaData & OraMetaData.Type

    'Display the count of attributes belonging to the table
    MsgBox NumberOfAttributes & OraMetaData.Count

    'Attribute can be accessed using the explicit OraMetaData property: Attribute
    'The index can be an integer or the attribute name
    Set OraMDAttribute = OraMetaData.Attribute(0)
    MsgBox "ObjectID: " & OraMDAttribute.Value

    'Since Attribute is the default property of OraMetaData, an attribute can
    ' be accessed as follows. Here, we use attribute name as an index
    Set OraMDAttribute = OraMetaData("ObjectID")
    MsgBox "Name: " & OraMDAttribute.Name
    MsgBox "Value: " & OraMDAttribute.Value

    'Value is the default property of OraMDAttribute, the following shows
    'the Value of property "IsClustered" for the table
    MsgBox "Is Clustered: " & OraMetaData!IsClustered
    MsgBox "Is Partitioned: " & OraMetaData!IsPartitioned

    'Retrieve the Column List
    Set OraMDAttribute = OraMetaData!ColumnList

    ' Use IsMDObject property to check whether an attribute's value is an OraMetaData
    If (OraMDAttribute.IsMDObject()) Then
           Set ColumnList = OraMDAttribute.Value
          'Display the name and data type of each column
           For I = 0 To ColumnList.Count - 1
            Set Column = ColumnList(I).Value

    ' Each column is again an OraMetaData
        MsgBox "Column: " & Column!Name & " data type: " & Column!Data Type
      Next I
    End If

Example: Describing a User-Defined Type

Before running the following example, make sure that you have the necessary datatypes and tables in the database. See "Schema Objects Used in OraMetaData Examples".

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim OraDynaset As OraDynaset
    Dim OraMetaData As OraMetaData
    Dim OraMDAttribute As OraMDAttribute
    Dim attrList As OraMetaData
    Dim attr As OraMetaData

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDB", "scott/tiger",0&)
    Set OraMetaData = OraDatabase.Describe("ORAMD_ADDRESS")
    NumAttributes = OraMetaData!NumAttributes
    NumMethods = OraMetaData!NumMethods
    MsgBox "The Address type has " & NumAttributes & " attributes"
    MsgBox "Address Object has " & NumMethods & " methods"

    'Retrieve the attribute list of this type object
    Set attrList = OraMetaData!Attributes.Value

    'Display the name and data type of each attribute
    For I = 0 To attrList.Count - 1
      Set attr = attrList(I).Value
      ' each attr is actually an OraMetaData
      MsgBox "Attribute Name: " & attr!Name
      MsgBox "Attribute Type: " & attr!TypeName

    Next I

Example: Describing Unknown Schema Objects

Before running the following example, make sure that you have the necessary datatypes and tables in the database. See "Schema Objects Used in OraMetaData Examples".

    Sub RecursiveDescribe(name$, xMD As OraMetaData)

    Dim xMDAttr As OraMDAttribute
    For I = 0 To xMD.Count - 1
        Set xMDAttr = xMD.Attribute(I)

        ' If an attribute can be described further, describe it,
        ' otherwise display its attribute name & value
        If (xMDAttr.IsMDObject) Then
            RecursiveDescribe xMDAttr.name, xMDAttr.Value
        Else
            MsgBox name & "->" & xMDAttr.name & " = " & xMDAttr.Value
      End If
    Next I

    End Sub
    Sub Main()

    'This example displays all the attributes of any schema object given
    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim OraDynaset As OraDynaset
    Dim xMD As OraMetaData
    Dim x As String

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDB", "scott/tiger", 0&)

    ' x is any database object, here the EMP table is used as an example
    x = "EMP"
    Set xMD = OraDatabase.Describe(x)
    MsgBox x & " is of the type " & xMD.Type
    RecursiveDescribe x, xMD

    End Sub

See Also: OraMetaData Object OraMDAttribute Object

## DestroyDatabasePool Method

Applies To

OraSession Object

Description

The pool is implicitly destroyed if its parent session object is destroyed. It can also be destroyed at any time by invoking the `DestroyDatabasePool` method.

Usage

    DestroyDatabasePool()

Remarks

An exception is raised by this call if the pool does not exist.

See Also: CreateDatabasePool Method

## DisableBuffering (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

Description

Disables buffering of LOB operations.

Usage

    OraBlob.DisableBuffering
    OraClob.DisableBuffering

Remarks

This method does not automatically flush the buffers. The `FlushBuffer` method should be used to flush any changes before buffering is disabled.

See Also: EnableBuffering (OraLOB) Method FlushBuffer (OraLOB) Method

## Div (OraIntervalDS) Method

Applies To

OraIntervalDS Object

Description

Divides the `OraIntervalDS` object by a divisor.

Usage

    OraIntervalDSObj.Div divisor

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `divisor` | A `Variant` for type numeric value or an `OraNumber` object to be used as the divisor.

Remarks

The result of the operation is stored in the `OraIntervalDS` object, overwriting any previous value. There is no return value.

## Div (OraIntervalYM) Method

Applies To

OraIntervalYM Object

Description

Divides the `OraIntervalYM` object by a divisor.

Usage

    OraIntervalYMObj.Div divisor

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `divisor` | A `Variant` for type numeric value or an `OraNumber` object to be used as the divisor.

Remarks

The result of the operation is stored in the `OraIntervalYM` object, overwriting any previous value. There is no return value.

## Div (OraNumber) Method

Applies To

OraNumber Object

Description

Divides an `OraNumber` object by a numeric argument.

Usage

    OraNumber.Div operand

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `operand` | A `Variant` of type `String`, `OraNumber` object, or a numeric value.

Remarks

The result of the operation is stored in an `OraNumber` object . There is no return value.

The `operand` must not be equal to zero, or a divide by zero error is raised.

## DynasetCacheParams Method

Applies To

OraParameter Object

Description

Specifies the dynaset cache and fetch parameters for the dynaset created from the PL/SQL cursor.

Usage

    oraparameter.DynasetCacheParams SliceSize,perblock, Blocks, FetchLimit,FetchSize

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`SliceSize` | Cache slice size.
`perblock` | Cache slices for each block.
`Blocks` | Cache maximum number of blocks.
`FetchLimit` | Fetch array size.
`FetchSize` | Fetch array buffer size.

Remarks

This method should be called before executing the PL/SQL procedure containing a cursor variable. By default, the dynaset is created with default cache and fetch parameters specified in the registry.

## Edit Method

Applies To

OraDynaset Object

Description

Begins an edit operation on the current row by copying the data to the copy buffer.

Usage

    oradynaset.Edit
    oradynaset.DbEdit

Remarks

The `Edit` method causes the locally cached data to be compared to the corresponding row of an Oracle Database. An error is generated if Oracle Database data is not the same as the data currently being browsed. If this operation succeeds, the row is locked using a `"SELECT` `...` `FOR` `UPDATE"` statement until the edit is completed with an `Update` method or until database movement occurs, which discards any edits in progress. The behavior of the `"SELECT` `...` `FOR` `UPDATE"` statement is affected by the `Lock` `Wait` mode of the options flag used when the `OpenDatabase` method was called.

Note: The cached data is not compared to the database with `BLOB` and `CLOB`, `Object`, `REF`, and collection types, and the data is updated regardless (dirty writes).

During editing, changes made to fields are kept in a shadowed copy buffer and do not yet reflect the actual contents of the database. However, all references to the row return the newly modified data as long as the edit operation is still in progress.

When data is modified within a data control attached to this dynaset, the `Edit` method is invoked automatically upon the next record movement. Thus, this method is required only when modifications are made to field data within code.

Note: A call to an `Edit`, `AddNew`, or `Delete` method cancels any outstanding `Edit` or `AddNew` calls before proceeding. Any outstanding changes not saved using an `Update` operation are lost during the cancellation.

Examples

This example demonstrates the use of the `Edit` and `Update` methods to update values in a database. Copy and paste this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

    'Declare variables
    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim OraDynaset As OraDynaset

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'Create the OraDynaset Object.
    Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)

     'Traverse until EOF is reached, settingeach employee's salary to zero
     Do Until OraDynaset.EOF
       OraDynaset.Edit
       OraDynaset.Fields("sal").value = 0
       OraDynaset.Update
       OraDynaset.MoveNext
     Loop
     MsgBox "All salaries set to ZERO."

    End Sub

See Also: AddNew Method CreateDynaset Method Delete Method OpenDatabase Method Update Method

## Edit (OraRef) Method

Applies To

OraRef Object

Description

Locks a referenceable object in the database.

Usage

    OraRef.Edit

Remarks

Call this method before modifying any attributes of an underlying referenceable object of `OraRef` or an error is raised. This call makes a network round-trip to lock the object in the database. An error is raised if the object is changed by another user in the database. The object can also be locked during the pin operation using the `EditOption` property.

Examples

The following examples update the attributes of the `"PERSON"` referenceable object in the database.

Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in the OraObject and OraRef Examples".

Dynaset Example

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim Person as OraRef

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create a dynaset object from customers
    set OraDynaset = OraDatabase.CreateDynaset("select * from customers", 0&)

    'retrieve a aperson column from customers.
    'Here Value property of OraField object 'returns Person OraRef
    set Person = OraDynaset.Fields("aperson").Value

    'locks the Person object in the server for modifying its attributes
    Person.Edit
      Person.Name = "Eric"
      Person.Age = 35
    'Update method flushes the modified referenceable object in the server
    Person.Update

Parameter Example

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim Person as OraRef

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create an  OraParameter object represent Address object bind Variable
    OraDatabase.Parameters.Add "PERSON", Null, ORAPARM_OUTPUT, _
                     ORATYPE_REF,"PERSON"

    'execute the sql statement which selects person from the customers table
    OraDatabase.ExecuteSQL ("BEGIN select aperson into :PERSON" & _
                     "from customers where account = 10;  END;")

    'get the Person object from OraParameter
    set Person = OraDatabase.Parameters("PERSON").Value

    'locks the Person object in the server for modifying its attributes
    Person.Edit
      Person.Name = "Eric"
      Person.Age = 35

    'Update method flushes the modified referenceable object in the server
    Person.Update

See Also: EditOption (OraRef) Property

## ElementValue Method

Applies To

OraCollection Object

Description

Returns the current value of the collection element to which the iterator points.

Usage

    elem_val = OraCollection.ElementValue

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`elem_val` | A `Variant` representing element value of the collection.

ElementType

For elements of type `Object` and `REF`, element values are returned as corresponding OO4O objects for that type. The following table shows the element type and return value of the elements:

ElementType | Element Value
--- | ---
`Object` | `OraObject`
`REF` | `OraRef`
`Date` | `String`
`Number` | `String`
`CHAR`,`VARCHAR2` | `String`
`Real` | `Real`
`Integer` | `Integer`

Remarks

Calling this method when the `EOC` or `BOC` property returns `True` raises an error. The `Variant` type of the element depends on the element type of the collection.

Examples

See "Example: OraCollection Iterator"

See Also: CreateIterator Method IterNext Method IterPrev Method

## EnableBuffering (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

Description

Enables buffering of LOB operations.

Usage

    OraBlob.EnableBuffering
    OraClob.EnableBuffering

Remarks

When enabled, buffering uses the LOB Buffering subsystem to minimize network round-trips by buffering changes until the `FlushBuffer` method is called. This can be beneficial to applications that perform a series of repeated small reads and writes to specific areas of a LOB.

There are many caveats and restrictions for using LOB buffering. These are summarized here, but for complete information, see the Oracle Database Application Developer's Guide - Large Objects.

Restrictions

- The following LOB methods cannot be used while buffering is enabled:

  - `Append`

  - `Copy`

  - `Erase`

  - `Size`

  - `Trim`

  - `CopyFromBFILE`

  - `CopyFromFile`

  - `CopyToFile`

- There is currently a 512 KB limit to the amount of a single read/write operation.

- Error reporting for buffered operations is delayed until the next database access.

- Transactional support is not guaranteed. Users must roll back changes manually if an error occurs.

- Do not perform updates to a LOB column that bypasses the buffering system while in the same transaction as a buffer-enabled LOB. Performing an `INSERT` statement can cause this.

- Only one LOB object is allowed to perform buffered writes to a given LOB. Other LOB objects that point to the same LOB raise an error if they attempt a buffered write.

- A LOB object taken from an `OraParameter` object raises an error if it is buffer-enabled and bound to an `OUT` parameter.

- The `Clone` method can raise an error for buffer enabled LOBs.

- Appending directly to the end of the LOB is allowed, but any write operation whose offset extends beyond the end of the LOB and results in blank padding (for `CLOB`) or zero padding (for `BLOB`) raises an error.

See Also: Oracle Database Application Developer's Guide - Large Objects

## Enqueue (OraAQ) Method

Applies To

OraAQ Object

Description

Enqueues the message (`OraAQMsg`) contained in this object.

Usage

    Msgid = Q.Enqueue

Remarks

On success, this method returns the message identifier as an array of bytes. Otherwise, it returns an empty array (null).

Examples

Note: The following code samples are models for enqueuing messages, but cannot be run as is. A complete AQ sample can be found in the `\OO4O\VB\SAMPLES\AQ` directory.

Enqueuing Messages of Type RAW

    'Create an OraAQ object for the queue "DBQ"
    Dim Q as OraAQ
    Dim Msg as OraAQMsg
    Dim OraSession as OraSession
    Dim DB as OraDatabase

    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    Set DB = OraSession.OpenDatabase("mydb", "scott/tiger" 0&)
    Set Q = DB.CreateAQ("DBQ")

    'Get a reference to the AQMsg object
    Set Msg = Q.AQMsg
    Msg.Value = "Enqueue the first message to a RAW queue."

    'Enqueue the message
    Q.Enqueue

    'Enqueue another message.
    Msg.Value = "Another message"
    Q.Enqueue

    'Enqueue a message with non-default properties.
    Msg.Priority = ORAQMSG_HIGH_PRIORITY
    Msg.Delay = 5
    Msg.Value = "Urgent message"
    Q.Enqueue
    Msg.Value = "The visibility option used in the enqueue call" & _
               "is ORAAQ_ENQ_IMMEDIATE"
    Q.Visible = ORAAQ_ENQ_IMMEDIATE
    Msgid = Q.Enqueue

    'Enqueue Ahead of message Msgid_1
    Msg.Value = "First Message to test Relative Message id"
    Msg.Correlation = "RELATIVE_MESSAGE_ID"

    Msg.delay = ORAAQ_MSG_NO_DELAY
    Msgid_1 = Q.Enqueue
    Msg.Value = "Second message to test RELATIVE_MESSAGE_ID is queued" & _
                " ahead of the First Message "
    Q.RelMsgId = Msgid_1
    Msgid = Q.Enqueue

Enqueuing Messages of Oracle Object Types

    'Prepare the message. MESSAGE_TYPE is a user defined type in the "AQ" schema
    Set OraMsg = Q.AQMsg(23, "MESSAGE_TYPE","SCOTT")
    Set OraObj = DB.CreateOraObject("MESSAGE_TYPE")

    OraObj("subject").Value = "Greetings from OO4O"
    OraObj("text").Value = "Text of a message originated from OO4O"

    Msgid = Q.Enqueue

## Erase (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

Description

Erases the specified portion of the LOB value of this object starting at the specified offset.

Usage

    OraBlob.Erase amount, offset
    OraClob.Erase amount, offset

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `amount` | An `Integer` specifying the maximum number of characters or bytes to be erased.
[`in`] `offset` [optional] | An `Integer` specifying absolute offset of the LOB value from which to start erasing. Default value is 1.

Remarks

Obtain either a row-level lock or object-level lock before calling this method. The actual number of characters or bytes and the requested number differ if the end of the LOB value is reached before erasing the requested number of characters or bytes. For `BLOB` types, erasing means that zero-byte fillers overwrite the existing LOB value. For `CLOB` types, erasing means that spaces overwrite the existing LOB value.

## ExecuteSQL Method

Applies To

OraDatabase Object

Description

Executes a single non-`SELECT` SQL statement or a PL/SQL block.

Usage

    rowcount = oradatabase.ExecuteSQL(sql_statement)
    rowcount = oradatabase.DbExecuteSQL(sql_statement)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`sql_statement` | Any valid Oracle non-`SELECT` SQL statement.

Remarks

Executes a SQL statement and returns the number of rows processed by that statement.

The `sql_statement` argument can be one continuous line with no breaks. If it is necessary to break the line, be sure to use line feeds (ASCII 10). Do not use carriage returns (ASCII 13), because the underlying Oracle Database functions treat carriage returns as null terminators.

Executing the SQL statement generates a commit to the database by default. To avoid this, use the `BeginTrans` method on the session object before using the `ExecuteSQL` method.

You can use PL/SQL bind variables in conjunction with the `OraParameters` collection.

When executing PL/SQL blocks or calling stored procedures, you must include a `BEGIN` and `END` statement around your call as if you were executing an anonymous PL/SQL block. This is equivalent to the `EXECUTE` command of SQL*Plus and SQL*DBA.

Note: The `ExecuteSQL` method should be used with care because any SQL statement or PL/SQL block that is executed can adversely affect open dynasets. This is true if the `OraDatabase` object used for the `ExecuteSQL` method is the same as the one that was used to create the dynaset. Use a different `OraDatabase` object if you are unsure.

Normal dynaset operations can be adversely affected, if in transactional mode, a database commit is issued. This can happen if a SQL commit statement, a Data Control Language (DCL), or Data Definition Language (DDL) command is issued. DCL and DDL SQL commands, such as `CREATE`, `DROP`, `ALTER`, `GRANT`, and `REVOKE` always force a commit, which in turn commits everything done before them. See the Oracle Database SQL Reference for more details about DCL, DDL, and transactions.

Data Type

`Long` `Integer`

Examples

Example: ExecuteSQL

This example uses the `Add` and `Remove` parameter methods, the `ServerType` parameter property, and the `ExecuteSQL` database method to call the stored procedure `GetEmpName` and the stored function `GetSal`. Before running the example, run the `ORAEXAMP.SQL` file to create `GetEmpName` and `GetSal` as well as other necessary object types and LOBs in Oracle Database. Then, copy and paste this OO4O code example into the definition section of a form and run the program.

    Sub Form_Load ()

    'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDatabase

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")

     'Create the OraDatabase Object.
     Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

     'Add EMPNO as an Input/Output parameter and set its initial value.
     OraDatabase.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT
     OraDatabase.Parameters("EMPNO").ServerType = ORATYPE_NUMBER

     'Add ENAME as an Output parameter and set its initial value.
     OraDatabase.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT
     OraDatabase.Parameters("ENAME").ServerType = ORATYPE_VARCHAR2

     'Add SAL as an Output parameter and set its initial value.
     OraDatabase.Parameters.Add "SAL", 0, ORAPARM_OUTPUT
     OraDatabase.Parameters("SAL").ServerType = ORATYPE_NUMBER

     'Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME.
     ' This Stored Procedure can be found in the file ORAEXAMP.SQL.
     OraDatabase.ExecuteSQL ("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;")
     'Display the employee number and name.

     'Execute the Stored Function Employee.GetSal to retrieve SAL.
     ' This Stored Function can be found in the file ORAEXAMP.SQL.
     OraDatabase.ExecuteSQL ("declare SAL number(7,2); Begin" & _
                       ":SAL:=Employee.GetEmpSal (:EMPNO); end;")

     'Display the employee name, number and salary.
     MsgBox "Employee " & OraDatabase.Parameters("ENAME").value & ", #" & _
               OraDatabase.Parameters("EMPNO").value & ",Salary=" & _
               OraDatabase.Parameters("SAL").value

     'Remove the Parameters.
     OraDatabase.Parameters.Remove "EMPNO"
     OraDatabase.Parameters.Remove "ENAME"

     OraDatabase.Parameters.Remove "SAL"
    End Sub

See Also: Oracle Database SQL Reference CreateDynaset Method OraParameters Collection Transactions Property

## Exist (OraCollection) Method

Applies To

OraCollection Object

Description

Returns `True` if an element exists at a given index; otherwise, returns. Valid only for `OraCollection` of Type `ORATYPE_TABLE`.

Usage

    exists = OraCollection.Exist index

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`out`] `exists` | A Boolean value specifying the existence status of the element.
[`in`] `index` | An `Integer` specifying the index of the element.

Remarks

None.

## Exp (OraNumber) Method

Applies To

OraNumber Object

Description

Calculates e to the power of an `OraNumber` object.

Usage

    OraNumber.Exp

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

## FetchOraRef Method

Applies To

OraDatabase Object

Description

Fetches a referenceable object into the cache and returns the associated `OraRef` object.

Usage

    Set OraRef = OraDatabase.FetchOraRef(hex_value)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`hex_value` | A `String` containing the hexadecimal value of the `REF`.

Remarks

The `hex_value` argument can be obtained through the `OraRef.HexValue` property or from an XML document generated by the `OraDynaset.GetXML` method.

See Also: HexValue (OraRef) Property GetXML Method

## FieldSize Method

Applies To

OraField Object

Description

Returns the number of bytes stored in a `LONG` or `LONG` `RAW` field. Not available at design time and read-only at run time.

Usage

    data_size = orafield.FieldSize( )
    data_size = orafield.DbFieldSize( )

Remarks

Returns the number of bytes stored in a `LONG` or `LONG` `RAW` field, up to a value of around 64 KB. If the field contains more than 64 KB, then the `FieldSize` method returns `-1`.

Oracle Database does not return the length of columns that are greater than 64 KB; The only way to determine the length is to retrieve the column. To conserve resources, columns of lengths greater than 64 KB are not retrieved automatically.

Data Type

Long Integer

See Also: AppendChunk Method GetChunk Method OraField Object Type Property

## FindFirst, FindLast, FindNext, and FindPrevious Methods

Applies To

OraDynaset Object

Description

Find the indicated rows in the dynaset that matches the `FindClause`. The `FindClause` can be any valid `WHERE` clause without the `WHERE`. If the current `FindClause` matches the last clause from the previous find operation, then the current `FindClause` is not parsed again.

These methods move the current row directly to a matched row without calling any advisories except when the matched row is reached. If a matching row cannot be found, the `NoMatch` property is set to `True`, and the current row remains the same.

Usage

    oradynaset.FindFirst FindClause
    oradynaset.FindLast FindClause
    oradynaset.FindNext FindClause
    oradynaset.FindPrevious FindClause

Remarks

The following types of expressions can be used in the `FindClause`:

- Simple queries, such as `"deptno = 20"`

- Queries involving complex expressions, such as `"sal` `+` `100` `>` `1000"`.

- SQL function calls, such as `"UPPER(ename)` `=` `'SCOTT'` `"` or `"NVL(comm,` `0)` `=` `0"`.

- Subqueries, such as `"deptno` `in` `(select` `deptno` `from` `dept)"`.

The SQL `LIKE` operator does not work in multiple byte languages. Table or synonym `DUAL` is required in the user's schema. Date values are retrieved and compared in Visual Basic format, which is the format specified in the Control Panel. Therefore, date comparisons fail if any other format such as the default Oracle format, DD-MON-YYYY is used.

The SQL function `TO_CHAR` (`date`, `fmt`) cannot be used because the first argument must be a date value in native Oracle format, and OO4O only handles `'string` `dates'`.

The SQL function `TO_DATE` converts a string to a date, but OO4O converts it back to a string in Visual Basic format, as previously described, and the comparison may still fail.

The `FindPrevious` and `FindLast` methods in a `NO_CACHE` dynaset do not work; `NoMatch` is set to `True`.

Note: To avoid raising an error, check for `EOF` or `BOF` before calling a `Find` method.

Examples

This example demonstrates the use of the `FindFirst`, `FindNext`, `FindPrevious` methods. Copy and paste this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

      Dim OraSession As OraSession
      Dim OraDatabase As OraDatabase
      Dim OraDynaset As OraDynaset
      Dim OraFields As OraFields
      Dim FindClause As String

      Set OraSession = CreateObject("OracleInProcServer.XOraSession")
      Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "SCOTT/TIGER", 0&)
      Set OraDynaset = OraDatabase.CreateDynaset("select * from emp where empno" & _
                     ">= 7654 and empno <= 7844 ", ORADYN_NO_BLANKSTRIP)

      Set OraFields = OraDynaset.Fields

      OraDynaset.MoveFirst

      'FindClause for job as MANAGER
      FindClause = "job LIKE '%GER'"

      OraDynaset.FindFirst FindClause

      'NoMatch property set to true , if no rows found
      If OraDynaset.NoMatch Then
        MsgBox "Couldn't find rows "
      else
        MsgBox OraFields("ename").Value  ' Should display BLAKE

        OraDynaset.FindNext FindClause
        MsgBox OraFields("ename").Value  ' Should display CLARK

        OraDynaset.FindPrevious FindClause
        MsgBox OraFields("ename").Value  ' Should display BLAKE

       endif

    End Sub

See Also: MoveFirst, MoveLast, MoveNext, and MovePrevious Methods NoMatch Property OraDatabase Object

## Floor (OraNumber) Method

Applies To

OraNumber Object

Description

Calculates the floor, that is, lowest value, of an `OraNumber` object.

Usage

    OraNumber.Floor

Remarks

The result of the operation is stored in an `OraNumber` object. There is no return value.

## FlushBuffer (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

Description

Flushes, that is, empties, the content of the LOB to the database if LOB buffering has been enabled.

Usage

    OraBlob.FlushBuffer OraClob.FlushBuffer

See Also: EnableBuffering (OraLOB) Method

## GetDatabaseFromPool Method

Applies To

OraSession Object

Description

Returns the next available `OraDatabase` object from the pool.

Usage

    GetDatabaseFromPool(long waitTime)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`waitTime` | The number of milliseconds this call waits for an object to be available, if the pool contains the maximum number of objects and all are used.

Remarks

To retrieve an `OraDatabase` object from the pool, the `GetDatabaseFromPool` method is called. This function returns a reference to an `OraDatabase` object. If the pool does not contain the maximum number of objects allowed, and all objects in the pool are used, then an additional `OraDatabase` object is created implicitly. In addition, if a pool item contains an `OraDatabase` object that has been timed out, then a new object is created and returned. The `OraDatabase` object obtained from the pool is then marked as in use and is returned to the pool when the object is no longer referenced by the application.

Exceptions are raised by this call if:

- The connection pool does not exist.

- The pool contains no objects.

- A time-out has occurred.

The `LastServerErr` property of the `OraSession` object contains the code for the specific cause of the exception.

See Also: CreateDatabasePool Method

## GetChunk Method

Applies To

OraField Object

Description

Returns a string containing the bytes of all or a portion of a `LONG` or `LONG` `RAW` field.

Usage

    data_string = orafield.GetChunk(offset, numbytes)
    data_string = orafield.DbGetChunk(offset, numbytes)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`offset` | The number of bytes of the field to skip before copying data.
`numbytes` | The number of bytes to copy.

Remarks

The `GetChunk` method typically retrieves the specified bytes from the local cache. If data is not found in the cache, then the `GetChunk` method requests it from the database. Data from all fields (except the LONG or `LONG` `RAW` field) in the dynaset are retrieved and compared to the cached values for consistency. If any changes have occurred since the last fetch, then the `GetChunk` method stops the operation which causes an error and returns a `Null` string.

If a `LONG` or `LONG` `RAW` field is less than 65280 bytes, it is quicker to retrieve the data using the `Value` property than using the `GetChunk` method. You cannot use the `GetChunk` method on a `LONG` or `LONG` `RAW` field for which you have created an alias.

See "Migration from LONG RAW to LOB or BFILE".

Examples

This example demonstrates the use of the `GetChunk` method to retrieve a `LONG` `RAW` column of a database and save it as a file. This example expects a valid dynaset named `OraDynaset` representing a table with a column named `longraw`. Copy and paste this code into the definition section of a form. Call this procedure with a valid file name.

    Sub GetChunkExample (FName As String)

    'Declare various variables
    Dim CurSize As Integer, ChunkSize  As Long
    Dim I As Integer, FNum As Integer, CurChunk As String

    'Set the size of each chunk
    ChunkSize = 10240

    frmChunk.MousePointer = HOURGLASS

    'Get a free file number
    FNum = FreeFile

    'Open the file
    Open FName For Binary As #FNum

     I = 0
    'Loop through all of the chunks. Oracle does not return the size of columns >
    ' 64KB. We should loop until the length of our block is less than we asked for.
    Do
     CurChunk = OraDynaset.Fields("LONGRAW").GetChunk(I * ChunkSize, ChunkSize)
     CurSize = Len(CurChunk) 'Get the length of the current chunk.

     Put #FNum, , CurChunk   'Write chunk to file.
     I = I + 1
    Loop Until CurSize < ChunkSize

    'Close the file.
    Close FNum

    frmChunk.MousePointer = DEFAULT

    End Sub

See Also: "Migration from LONG RAW to LOB or BFILE" AppendChunk Method FieldSize Method GetChunk Method Type Property Value Property OraField Object

## GetChunkByte Method

Applies To

OraField Object

Description

Reads the data from the `LONG` or `LONG` `RAW` field into byte array and returns the size of data read.

Usage

    Size_read = orafield.GetChunkByte(ByteArray, offset, numbytes)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`ByteArray` | The first element of the `ByteArray` to hold the data.
`offset` | The number of bytes in the field to skip before copying data.
`numbytes` | The number of bytes to copy.

Remarks

When possible, the `GetChunkByte` method retrieves the specified bytes from the local cache. However, to conserve resources, some of the data might not be stored locally. In these cases, the `GetChunkByte` method requests the necessary data from the database as required. As part of this process, data from all fields (except the Long or `LONG` `RAW` field) in the dynaset are retrieved and compared with the cached values for consistency. If any changes have occurred since the fetch of the original partial data, then the `GetChunkByte` method stops the operation and an error occurs. In the case of an abort, the returned string is `Null`.

If a `LONG` or `LONG` `RAW` field is less than 65280 bytes in size, it is quicker to retrieve the data using the `Value` property than using the `GetChunkByte` method. You cannot use the `GetChunkByte` method on a `LONG` or `LONG` `RAW` field for which you have created an alias.

Examples

This example demonstrates the use of the `GetChunkByte` method to retrieve a `LONG` `RAW` column of a database and save it as a file. This example expects a valid dynaset named `OraDynaset` representing a table with a column named `longraw`. Copy and paste this code into the definition section of a form. Call this procedure with a valid file name.

    Sub GetChunkByteExample (FName As String)

    'Declare various variables
    Dim CurSize As Integer, ChunkSize  As Long
    Dim I As Integer, FNum As Integer, CurChunk() As Byte

    'Set the size of each chunk
    ChunkSize = 10240
    'Redim CurChunk Array
    ReDim CurChunk(ChunkSize)

    frmChunk.MousePointer = HOURGLASS

    'Get a free file number
    FNum = FreeFile

    'Open the file
    Open FName For Binary As #FNum

     I = 0
    'Loop through all of the chunks
    'Oracle does not return the size of columns > 64KB. We should loop until the 'length of our block is less than we asked for.

    Do
     CurSize = OraDynaset.Fields("type_longraw").GetChunkByte(CurChunk(0), I * ChunkSize, ChunkSize)

    If CurSize > 0 AND CurSize < ChunkSize Then
        ReDim CurChunk(CurSize)
        CurSize = OraDynaset.Fields("type_longraw").GetChunkByte(CurChunk(0), I * ChunkSize, CurSize)
     End If
     Put #FNum, , CurChunk   'Write chunk to file.
     I = I + 1
    Loop Until CurSize <= 0

    'Close the file.
    Close FNum

    frmChunk.MousePointer = DEFAULT

    End Sub

See Also: "Migration from LONG RAW to LOB or BFILE" for additional information

## GetChunkByteEx Method

Applies To

OraField Object

Description

Reads the data from a `LONG` or `LONG` `RAW` field into a `Variant` and returns the amount of data read.

Usage

    amount_read = orafield.GetChunkByteEx(ByteArray, offset, numbytes)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`ByteArray` | The name of the `Variant` `ByteArray` to hold the data.
`offset` | The number of bytes in the field to skip before copying data.
`numbytes` | The number of bytes to copy.

Remarks

When possible, the `GetChunkByteEx` method retrieves the specified bytes from the local cache. However, to conserve resources, some of the data might not be stored locally. In these cases, the `GetChunkByteEx` method requests the necessary data from the database as required. As part of this process, data from all fields (except the `LONG` or `LONG` `RAW` field) in the dynaset are retrieved and compared to the cached values for consistency. If any changes have occurred since the fetch of the original partial data, then the `GetChunkByteEx` method aborts the operation with an error.

Because the `GetChunkByteEx` method takes in a `Variant` as the first parameter, instead of the first element of the `ByteArray` as in the `GetChunkByte` method, only the `GetChunkByteEx` method can be used within an ASP/IIS environment.

If a `LONG` or `LONG` `RAW` field is less than 65280 bytes in size, it is quicker to retrieve the data using the `Value` property than using the `GetChunkByteEx` method.

See "Migration from LONG RAW to LOB or BFILE".

Examples

Using the GetChunkByteEx Method to Retrieve a LONG RAW Example

This example demonstrates the use of the `GetChunkByteEx` method to retrieve a `LONG` `RAW` column of a database and save it as a file. This example expects a valid dynaset named `OraDynaset` representing a table with a column named `type_longraw`. Copy and paste this code into the definition section of a form. Call this procedure with a valid file name.

    Sub GetChunkByteExExample (FName As String)
    'Declare various variables
    Dim bytesread As Integer, ChunkSize As Long ,

    bytearr() as byte
    Dim I As Integer, FNum As Integer, CurChunk
    'Set the size of each chunk
    ChunkSize = 10240

    frmChunk.MousePointer = HOURGLASS
    'Get a free file number
    FNum = FreeFile
    'Open the file
    Open FName For Binary As #FNum
    I = 0
    'Loop through all of the chunks
    'Oracle does not return the size of columns > 64KB.
    'We should loop until the length of our block is
    'less than we asked for.
    Do
      bytesread = OraDynaset.Fields("type_longraw").GetChunkByteEx(CurChunk,_
                  I * ChunkSize, ChunkSize)
    'redim byte array
    redim bytearr(bytesread - 1)
    bytearr = CurChunk
    Put #FNum, , bytearr 'Write chunk to file.
    I = I + 1
    Loop Until bytesread < ChunkSize
    'Close the file.
    Close FNum
    frmChunk.MousePointer = DEFAULT
    End Sub

Using the GetChunkByteEx Method with Active Server Pages (ASP) Example

    'This example is for use with ASP (Active Server Pages)
    <%@ LANGUAGE = VBScript %>
    <%Response.ContentType = "image/JPEG"%>
    <%
    Dim OraDatabase, Oradynaset
    Dim Chunksize, BytesRead, CurChunkEx
    'This assumes a pool of database connections have been created in the global.asa
    Set OraDatabase = OraSession.getDatabaseFromPool(10)
    'This assumes a table called "art_gallery" and
    'displays JPEG images stored in the table
    Set OraDynaset = OraDatabase.CreateDynaset("select art from art_gallery " & _
                  "where artist = 'Picasso'", 0)

    BytesRead = 0
    'Reading in 32K chunks
    ChunkSize= 32768
    Do
      BytesRead = OraDynaset.Fields("picture").GetChunkByteEx(CurChunkEx, _
                                     i * ChunkSize, ChunkSize)
      if BytesRead > 0 then
         Response.BinaryWrite CurChunkEx
       end if
    Loop Until BytesRead < ChunkSize
    'Cleanup, remove all local references
    Set OraDynaset = Nothing
    Set Oradatabase = Nothing
    %>

See Also: "Migration from LONG RAW to LOB or BFILE"

## GetXML Method

Applies to

OraDynaset Object

Description

Generates an XML document based on the contents of the dynaset.

Usage

    XMLstring = oradynaset.GetXML(startrow, maxrows)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`startrow` | The row identifier indicating from which row to start (see `OraDynaset.RowPosition`). The default value of this argument is zero (the first row).
`maxrows` | The maximum number of rows to retrieve (if the end of the record set is reached; fewer rows may be returned). If this argument is omitted, then all rows are returned.

Remarks

This method returns a string containing the XML document.

The formatting of the output XML can be customized through the XML properties of the `OraDynaset` and `OraField` objects.

See Also: OraDynaset Object OraField Object RowPosition Property

## GetXMLToFile Method

Applies To

OraDynaset Object

Description

Generates an XML document and writes it to a file.

Usage

    oradynaset.GetXMLToFile (filename, startrow, maxrows)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`filename` | The file name that the XML is written to. Existing files by the same name are overwritten.
`startrow` | The row identifier indicating from which row to start (see `OraDynaset.RowPosition`). The default value of this argument is `0` (the first row).
`maxrows` | The maximum number of rows to retrieve (if the end of the record set is reached; fewer rows may be returned). If this argument is omitted, then all rows are returned.

Remarks

There is no return value.

The formatting of the XML output can be customized through the XML properties of the `OraDynaset` and `OraField` objects.

See Also: OraDynaset Object OraField Object RowPosition Property

## GetRows Method

Applies To

OraDynaset Object

Description

Retrieves multiple records of a dynaset object into `Variant` safe array.

Usage

    Array =OraDynaset.GetRows(num_rows, start, fields )

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`num_rows` [optional] | An `Integer` representing the number of records to retrieve. Default value is the total number of rows in the dynaset.
`start` [optional] | An `Integer` representing the starting position of the dynaset from which the `GetRows` operation begins. Default value is the current position of the dynaset.
`fields` [optional] | A `Variant` representing a single field name or field position, or an array of field names or array of field position numbers. The `GetRows` method returns only the data in these fields.

Remarks

Use the `GetRows` method to copy records from a dynaset into a two-dimensional array. The first subscript identifies the field and the second identifies the row number. The `Array` variable is automatically dimensioned to the correct size when the `GetRows` method returns the data.

Calling the `GetRows` method does not change the current row position of the dynaset object.

Examples

The following example retrieves data using the `GetRows` method.

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim OraDynaset As OraDynaset
    Dim row, col As Integer
    Dim fields() As String

    'Create the OraSession Object
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", _
                   "scott/tiger", 0&)

    Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)

    'The following line executes GetRows to get all records
    data_array = OraDynaset.GetRows()

    'Now display all the data in data_array
    For row = 0 To UBound(data_array, 2)
        For col = 0 To UBound(data_array, 1)
            Debug.Print data_array(col, row)
        Next col
    Next row

    'The following lines execute GetRows to get the data from
    'the ename and empno fields starting at 5

    ReDim fields(2)

    fields(0) = "EMPNO"
    fields(1) = "ENAME"

    'Execute GetRows
    data_array = OraDynaset.GetRows(, 5, fields)

    'Now display all the data in data_array
    For row = 0 To UBound(data_array, 2)
        For col = 0 To UBound(data_array, 1)
            Debug.Print data_array(col, row)
        Next col
    Next row

## Get_Value Method

Applies To

OraParamArray Object

Description

Returns the value of a particular element of the array at the specified index.

Usage

    OraParamArray.Get_Value(array, index)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `array` | A `String` representing the name of the array.
[`in`] `index` | An `Integer` representing the index value of the object.

Remarks

The `OraParamArray.Get_Value` method returns the value of the field as a `Variant`. The value of `data_value` `=` `oraparameter.Value` sets the contents of the parameter.

Note that fields of type `DATE` are returned in the default Visual Basic format as specified in the Control Panel, even though the default Oracle date format is "DD-MMM-YY".

The `Value` argument can be an Oracle Database 10g object, such as an `OraBLOB` object. For `Put_Value`, a copy of the object is made at that point in time, and `Get_Value` must be accessed to obtain a new object that refers to that index value. For example, if `iotype` is `ORATYPE_BOTH` and an `OraBLOB` object obtained from a dynaset is passed in as the input value, `Get_Value` needs to be called after the SQL code has been executed to obtain the newly updated output value of the `ParamaterArray` object.

Similar to a dynaset, the object obtained from the `ParamaterArray` `Get_Value` property refers to the latest value for that `ParamaterArray` index. The Visual Basic value `Null` can also be passed as a value. The Visual Basic value `EMPTY` can be used for `BLOB` and `CLOB` to indicate an empty LOB, and for `Object`, `VARRAY`, and nested table data types to indicate an object whose attributes are all `Null`.

This method is not available at design time and is read-only at run time.

When binding to `RAW` columns (`ServerType` `ORATYPE_RAW_BIN`), the value should be a byte array.

## HypCos (OraNumber) Method

Applies To

OraNumber Object

Description

Calculates the hyperbolic cosine of an `OraNumber` object.

Usage

    OraNumber.HypCos

Remarks

The result of the operation is stored in an `OraNumber` object. There is no return value.

## HypSin (OraNumber) Method

Applies To

OraNumber Object

Description

Calculates the hyperbolic sine of an `OraNumber` object.

Usage

    OraNumber.HypSin

Remarks

The result of the operation is stored in an `OraNumber` object. There is no return value.

## HypTan (OraNumber) Method

Applies To

OraNumber Object

Description

Calculates the hyperbolic tangent of an `OraNumber` object.

Usage

    OraNumber.HypTan

Remarks

The result of the operation is stored in an `OraNumber` object. There is no return value.

## InitIterator Method

Applies To

OraCollection Object

Description

Initializes an iterator to scan a collection.

Usage

    OraCollection.InitIterator

Remarks

This method initializes an iterator to point to the beginning of a collection. If this method is called for same Oracle Database 10g collection instance, then this method resets the iterator to point back to the beginning of the collection. The `OraCollection` object automatically reinitializes the iterator when the underlying collection changes due to a dynaset row navigation or a parameter `Refresh` method.

After you call the `InitIterator` method, you need to call the `IterNext` method or the first element in the collection repeats an extra time.

Examples

See "Example: OraCollection Iterator".

See Also: IterNext Method IterPrev Method

## IsEqual (OraIntervalDS) Method

Applies To

OraIntervalDS Object

Description

Checks if the `OraIntervalDS` object is equal to an argument.

Usage

    isEqual = OraIntervalDSObj.IsEqual value

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, a numeric value, or an `OraIntervalDS` object to be compared.

Remarks

Returns a Boolean value: The value is `True` if the `OraIntervalDS` object is equal to the argument; otherwise, it is `False`.

If `value` is a `Variant` of type `String`, it must be in the following format: [+/-] Day HH:MI:SSxFF.

If `value` is a numeric value, the value provided should represent the total number of days that the constructed `OraIntervalDS` object represents.

## IsEqual (OraIntervalYM) Method

Applies To

OraIntervalYM Object

Description

Checks if the `OraIntervalYM` object is equal to an argument.

Usage

    isEqual = OraIntervalYMObj.IsEqual value

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, a numeric value, or an `OraIntervalYM` object to be compared.

Remarks

Returns a Boolean value: The value is `True` if the `OraIntervalYM` object is equal to the argument; otherwise, it is `False`.

If `value` is a `Variant` of type `String`, it must be in the following format: [+/-] YEARS-MONTHS.

If `value` is a numeric value, the value provided should represent the total number of years that the constructed `OraIntervalYM` object represents.

## IsEqual (OraNumber) Method

Applies To

OraNumber Object

Description

Checks if an `OraNumber` object is equal to an argument value.

Usage

    bool  = OraNumber.IsEqual value

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, `OraNumber`, or a numeric value.

Remarks

Returns a Boolean value: The value is `True` if all values are equal; otherwise, it is `False`.

## IsEqual (OraTimeStamp) Method

Applies To

OraTimeStamp Object

Description

Checks if the `OraTimeStamp` object is equal to an argument.

Usage

    isEqual = OraTimeStampObj.IsEqual value format

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[in] `value` | A `Variant` of type `String`, `Date`, or `OraTimeStamp` to be compared.
[`in`] [optional] `format` | Specifies the `TIMESTAMP` format string to be used to interpret `value` when value is of type `String`. If `format` is not specified, the value is interpreted using the `Format` property of the current `OraTimeStamp` object.

Remarks

Returns a Boolean value: The value is `True` if the `OraTimeStamp` object is equal to the argument; otherwise, it is `False`. The `IsEqual` method compares all the date-time values stored in the `OraTimeStamp` object.

If `value` is of type `String`, the string format must match the format specified in the `format` argument. If `format` is not specified, the string format must match the `Format` property of the current `OraTimeStamp` object.

## IsEqual (OraTimeStampTZ) Method

Applies To

OraTimeStampTZ Object

Description

Checks if the `OraTimeStampTZ` object is equal to an argument.

Usage

    isEqual = OraTimeStampTZOb.IsEqual value, format

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, `Date`, or `OraTimeStampTZ` to be compared.
[`in`] [optional] `format` | Specifies the `TIMESTAMP` `WITH` `TIME` `ZONE` format string to be used to interpret `value` when `value` is type `String`. If `format` is not specified, `value` is interpreted using the `Format` property of the current `OraTimeStampTZ` object.

Remarks

Returns a Boolean value: The value is `True` if the `OraTimeStampTZ` object is equal to the argument; otherwise, it is `False`. The `IsEqual` method only compares the Coordinated Universal Time (UTC) date-time values stored in the `OraTimeStampTZ` object; the time zone information is ignored.

Note: UTC was formerly known as Greenwich Mean Time.)

If `value` is of type `String`, the string format must match the format specified in the `format` argument. If `format` is not specified, the string format must match the `Format` property of the current session `OraTimeStampTZ` object.

If `value` is of `Date` type, the date-time value in `Date` is interpreted as the date-time value in the time zone of the session.

## IsGreater (OraIntervalDS) Method

Applies To

OraIntervalDS Object

Description

Checks if the `OraIntervalDS` object is greater than an argument.

Usage

    isGreater = OraIntervalDSObj.IsGreater value

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, a numeric value, or an `OraIntervalDS` object to be compared.

Remarks

Returns a Boolean value: The value is `True` if the `OraIntervalDS` object is greater than the argument; otherwise, it is `False`.

If `value` is a `Variant` of type `String`, it must be in the following format: Day [+/-] HH:MI:SSxFF.

If `value` is a numeric value, the value provided should represent the total number of days that the constructed `OraIntervalDS` object represents.

## IsGreater (OraIntervalYM) Method

Applies To

OraIntervalYM Object

Description

Checks if the `OraIntervalYM` object is greater than an argument.

Usage

    isGreater = OraIntervalYMObj.IsGreater value

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, a numeric value, or an `OraIntervalYM` object to be compared.

Remarks

Returns a Boolean value: The value is `True` if the `OraIntervalYM` object is greater than the argument; otherwise, it is `False`.

If `value` is a `Variant` of type `String`, it must be in the following format: [+/-] YEARS-MONTHS.

If `value` is a numeric value, the value provided should represent the total number of years that the constructed `OraIntervalYM` object represents.

## IsGreater (OraNumber) Method

Applies To

OraNumber Object

Description

Checks if an `OraNumber` object is greater than an argument value.

Usage

    bool  = OraNumber.IsGreater value

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, `OraNumber` object, or a numeric value.

Remarks

Returns a Boolean value: The value is `True` if the `OraNumber` object is greater than the argument; otherwise, it is `False`.

## IsGreater (OraTimeStamp) Method

Applies To

OraTimeStamp Object

Description

Checks if the `OraTimeStamp` object is greater than an argument.

Usage

    isGreater = OraTimeStampObj.IsGreater value format

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, `Date`, or `OraTimeStamp` to be compared.
[`in`] [optional] `format` | Specifies the `TIMESTAMP` format string to be used to interpret value when `value` is of type `String`. If `format` is not specified, the value is interpreted using the `Format` property of the current `OraTimeStamp` object.

Remarks

Returns a Boolean value: The value is `True` if the `OraTimeStamp` object is greater than the argument; otherwise, it is `False`. The `IsGreater` method compares all the date-time values stored in the `OraTimeStamp` object.

If `value` is of type `String`, the string format must match the format specified in the `format` argument. If `format` is not specified, the string format must match the `Format` property of the current `OraTimeStamp` object.

## IsGreater (OraTimeStampTZ) Method

Applies To

OraTimeStampTZ Object

Description

Checks if the `OraTimeStampTZ object` is greater than an argument.

Usage

    isGreater = OraTimeStampTZObj.IsGreater value, format

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, `Date`, or `OraTimeStampTZ` object to be compared.
[`in`] [optional] `format` | Specifies the `TIMESTAMP` `WITH` `TIME` `ZONE` format string to be used to interpret a value when `value` is type `String`. If `format` is not specified, `value` is interpreted using the `Format` property of the current `OraTimeStampTZ` object.

Remarks

Returns a Boolean value: The value is `True` if the `OraTimeStampTZ` object is greater than the argument; otherwise, it is `False`. The `IsGreater` method only compares the UTC date-time values stored in the `OraTimeStampTZ` object; the time zone information is ignored.

If `value` is of type `String`, the string format must match the format specified in the `format` argument. If `format` is not specified, the string format must match the `Format` property of the current `OraTimeStampTZ` object.

If `value` is of type `Date`, the date-time value in `Date` is interpreted as the date-time value in the time zone of the session.

## IsLess (OraIntervalDS) Method

Applies To

OraIntervalDS Object

Description

Checks if the `OraIntervalDS` object is less than an argument.

Usage

    isLess = OraIntervalDSObj.IsLess value

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, a numeric value, or an `OraIntervalDS` object to be compared.

Remarks

Returns a Boolean value: The value is `True` if the `OraIntervalDS` object is less than the argument; otherwise, it is `False`.

If `value` is a `Variant` of type `String`, it must be in the following format: [+/-] Day HH:MI:SSxFF.

If `value` is a numeric value, the value provided should represent the total number of days that the constructed `OraIntervalDS object` represents.

## IsLess (OraIntervalYM) Method

Applies To

OraIntervalYM Object

Description

Checks if the `OraIntervalYM object` is less than an argument.

Usage

    isLess  = OraIntervalYMObj.IsLess value

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, a numeric value, or an `OraIntervalYM` object to be compared.

Remarks

Returns a Boolean value: The value is `True` if the `OraIntervalYM` object is less than the argument; otherwise, it is `False`.

If `value` is a `Variant` of type `String`, it must be in the following format: [+/-] YEARS-MONTHS.

If `value` is a numeric value, the value provided should represent the total number of years that the constructed `OraIntervalYM` object represents.

## IsLess (OraNumber) Method

Applies To

OraNumber Object

Description

Checks if an `OraNumber` object is less than an argument value.

Usage

    bool  = OraNumber.IsLess value

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, `OraNumber` object, or a numeric value.

Remarks

Returns a Boolean value: The value is `True` if the OraNumber object is less than the argument; otherwise, it is `False`.

## IsLess (OraTimeStamp) Method

Applies To

OraTimeStamp Object

Description

Checks if the `OraTimeStamp` object is less than an argument.

Usage

    isLessr = OraTimeStampObj.IsLess value format

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, `Date`, or `OraTimeStamp`.
[`in`] [optional] `format` | Specifies the `TIMESTAMP` format string to be used to interpret value when value is of type `String`. If format is not specified, the value is interpreted using the `Format` property of the current `OraTimeStamp` object.

Remarks

Returns a Boolean value: The value is `True` if the `OraTimeStamp` is less than the argument; otherwise, it is `False`. The `IsLess` method compares all the date-time values stored in the `OraTimeStamp` object.

If `value` is of type `String`, the string format must match the format specified in the `format` argument. If `format` is not specified, the string format must match the `Format` property of the current `OraTimeStamp` object.

## IsLess (OraTimeStampTZ) Method

Applies To

OraTimeStampTZ Object

Description

Checks if the `OraTimeSTampTZ` object is less than an argument.

Usage

    isLess = OraTimeStampTZObj.IsLess value, format

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` of type `String`, `Date`, or `OraTimeStampTZ`.
[[`in`] [optional] `format` | Specifies the `TIMESTAMP` `WITH` `TIME` `ZONE` format string to be used to interpret value when `value` is type `String`. If `format` is not specified, `value` is interpreted using the `Format` property of the current `OraTimeStampTZ` object.

Remarks

Returns a Boolean value: The value is `True` if the `OraTimeStampTZ` object is less than the argument; otherwise, it is `False`. `IsLess` only compares the UTC date-time values stored in the `OraTimeStampTZ` object; the time zone information is ignored.

If `value` is of type `String`, the string format must match the format specified in the `format` argument. If `format` is not specified, the string format must match the `Format` property of the current `OraTimeStampTZ` object.

If `value` is of type `Date`, the date-time value in `Date` is interpreted as the date-time value in the time zone of the session.

## IterNext Method

Applies To

OraCollection Object

Description

Moves the iterator to point to the next element in the collection.

Usage

    OraCollection.IterNext

Remarks

Using an iterator is faster than using an index when accessing collection elements.

If the iterator is pointing to the last element of the collection before to executing this function, then calling this method makes the `EOC` property return `True`. Also, the iterator is not changed. Check the `EOC` property when calling this method repetitively.

Call the `IterNext` method after the `InitIterator` method, or the first element in the collection is repeated an extra time.

Examples

See "Example: OraCollection Iterator" .

See Also: IterPrev Method InitIterator Method

## IterPrev Method

Applies To

OraCollection Object

Description

Moves the iterator to point to the previous element in the collection.

Usage

    OraCollection.IterPrev

Remarks

Using an iterator is faster than using an index when accessing collection elements.

If the iterator is pointing to the first element of the collection prior to executing this function, then calling this method makes the `BOC` property return `True`. Also, the iterator is not changed. Check the `BOC` property when calling this method repetitively.

Examples

See "Example: OraCollection Iterator" .

See Also: IterNext Method InitIterator Method

## LastServerErrReset Method

Applies To

OraDatabase Object

OraSession Object

Description

Clears the `LastServerErr` property to a zero value and sets the `LastServerErrText` property to `Null` for the specified object.

Usage

    oradatabase.LastServerErrResetorasession.LastServerErrReset

Remarks

This method allows user programs to better determine which program request generated the Oracle error.

See Also: OraDatabase Object OraSession Object LastServerErr Property LastServerErrText Property

## Ln (OraNumber) Method

Applies To

OraNumber Object

Description

Calculates the natural logarithm (base e) of an `OraNumber` object.

Usage

    OraNumber.Ln

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

This method raises an error if the `OraNumber` object is less than or equal to zero.

## Log (OraNumber) Method

Applies To

OraNumber Object

Description

Calculates the logarithm of `operand` using the `OraNumber` object as the base.

Usage

    OraNumber.Log operand

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `operand` | A `Variant` of type `String`, `OraNumber`, or a numeric value.

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

This method raises an error if the `OraNumber` object or `operand` is less than or equal to zero.

## MatchPos (OraLOB/BFILE) Method

Applies To

OraBLOB, OraCLOB Objects

OraBFILE Object

Description

Returns the position of the `nth` occurrence of the pattern starting at the offset.

Usage

    position = OraBlob.MatchPos pattern, offset, nth
    position = OraClob.MatchPos pattern, offset, nth
    position = OraBFile.MatchPos pattern, offset, nth

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `pattern` | A string for `CLOB`, or byte array for `BLOB` or `BFILE` that is searched for in the LOB.
[`in`] `Offset` | The starting position in the LOB or `BFILE` for the search.
[`in`] `nth` | The occurrence number.

Remarks

This call is currently implemented by executing a PL/SQL block that uses `DBMS_LOB.INSTR()`.

## Mod (OraNumber) Method

Applies To

OraNumber Object

Description

Gets the modulus from the division of the `OraNumber` object by `operand`.

Usage

    OraNumber.Mod operand

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `operand` | A `Variant` of type `String`, `OraNumber`, or a numeric value.

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

If `operand` is equal to zero, an error is raised.

## MonitorForFailover Method

Applies To

OraDatabase Object

Description

Registers the failover notification handler of the application.

Usage

    OraDatabase.MonitorForFailover FOSink, FOCtx

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `FOSink` | An `IDispatch` interface implementing the `OnFailover` method which is notified in event of a failover.
[`in`] `FOCtx` | Context-specific information that the application wants passed into the `OnFailover` method in the event of a failover.

Remarks

To receive failover notifications, a notification handler must be registered with the `MonitorForFailover` method. The notification handler must be an automation object (a class module in Visual Basic) that implements the `OnFailover` method.

The syntax of the method is:

    Public Function OnFailover(Ctx As Variant, fo_type As Variant,fo_event as Variant, fo_OraDB as Variant)

Variants | Description
--- | ---
`[in]` `Ctx` | Passed into the `MonitorForFailover` method by the application. Context-sensitive information that the application wants passed in event of a failover.
`[in]` `fo_type` | `Failover` type. This is the type of failover that the client has requested. The values are: `OO4O_FO_SESSION` indicates only session failover requested. `OO4O_FO_SELECT` indicates select failover and session failover requested.
`[in]` `fo_event` | `Failover` event. This indicates the state of the failover. It has several possible values: `OO4O_FO_BEGIN` indicates that failover has detected a lost connection and failover is starting. `OO4O_FO_END` indicates successful completion of a failover. `OO4O_FO_ABORT` indicates that a failover was unsuccessful, and there is no option of retrying. `OO4O_FO_ERROR` indicates that a failover was unsuccessful, and gives the application the opportunity to handle the error and retry the failover. The application can retry the failover, by programming the `OnFailover` method to return `OO4O_FO_RETRY`. `OO4O_FO_REAUTH` indicates that a user handle has been reauthenticated. This applies to the situation where a client has multiple user sessions on a single database connection. During the initial failover, only the active user session is failed over. Other sessions are failed over when the application tries to use them. This is the value passed to the callback during these subsequent failovers.
`[in]` `fo_OraDB` | The `OraDatabase` object of the user session that is being failed over. Valid only when the `fo_event` variant is `OO4O_FO_REAUTH`.

Examples

Failover Notification Example

See Example: Failover Notification.

See Also: "Application Failover Notifications"

## MonitorStart (OraAQ) Method

Applies To

OraAQ Object

Description

Starts a monitor thread for dequeuing the messages specified.

Usage

    Q.MonitorStart NotificationHandler, CallbackCtx, MsgFilterVal,MsgFilter

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `NotificationHandler` | An `IDispatch` interface containing the callback method (`NotifyMe`) which should be notified of new messages.
[`in`] `CallbackCtx` | Context-specific information that the application wants to pass to the `NotifyMe` method. This is passed into the `NotifyMe` method whenever a new message satisfying the user criteria is dequeued.
[`in`] [optional] `MsgFilterVal` | A byte array containing a value for the message filter. Ignored if `MsgFilter` is `ORAAQ_ANY`.
[`in`] [optional] `MsgFilter` | An `Integer` constant specifying the selection criteria for messages. Possible values for `MsgFilter` are: `ORAAQ_ANY` = `0` - Invokes the callback for any message that arrives on the queue. This is the default value. `ORAAQ_CONSUMER` = `1` - Invokes the callback when the message intended for the consumer given in the `MsgFilterValue` is dequeued. `ORAAQ_MSGID` = `2` - Invokes the callback when message with the identifier specified in `MsgFilterVal` is dequeued.

Remarks

`NotifyMe` is the callback method of the notification object. The syntax of the method is:

    Public Sub NotifyMe (ByVal Ctx As Variant, ByVal Msgid As Variant)

Variants | Description
--- | ---
`[in]` `Ctx` | Value passed into the `MonitorStart` method by the application. Context-sensitive information that the application wants to pass in when messages are dequeued.
`[in]` `Msgid` | The message ID of the newly dequeued message. The `Msgid` variant is null when there is an error while monitoring.

By default, the message is passed into `NotifyMe` in `Remove` mode. The default dequeue options can be overridden by setting the properties of this instance (`OraAQ`).

The `MonitorStart` method returns `ORAAQ_SUCCESS` or `ORAAQ_FAIL`.

See Also: "Monitoring Messages"

## MonitorStop (OraAQ) Method

Applies To

OraAQ Object

Description

Stops the monitor thread that was started earlier.

Usage

    Q.MonitorStop

Remarks

Does nothing if a monitor is not running.

See Also: "Monitoring Messages"

## MoveFirst, MoveLast, MoveNext, and MovePrevious Methods

Applies To

OraDynaset Object

Description

Change the cursor position to the first, last, next, or previous row within the specified dynaset. These move methods move the cursor to the next (previous, and so on) valid row, skipping rows that have been deleted.

Usage

    oradynaset.MoveFirst
    oradynaset.DbMoveFirst

    oradynaset.MoveLast
    oradynaset.DbMoveLast

    oradynaset.MovePrevious
    oradynaset.DbMovePrevious

    oradynaset.MoveNext
    oradynaset.DbMoveNext

Remarks

The data control buttons map (from left to right or from top to bottom) to the `MoveFirst`, `MovePrevious`, `MoveNext`, and `MoveLast` methods. The `BOF` and `EOF` properties are never true when using the data control buttons.

When the first or last record is current, record movement does not occur if you use the `MoveFirst` or `MoveLast` methods, respectively. You force the query to completion if you use the `MoveLast` method on a dynaset.

If you use the `MovePrevious` method and the first record is current, there is no current record and `BOF` is true. Using the `MovePrevious` method again causes an error, although `BOF` remains `True`. If you use the `MoveNext` method and the last record is current, there is no current record and `EOF` is true. Using the `MoveNext` method again causes an error, although `EOF` remains true. Note that when the dynaset is created with the `ORADYN_NO_MOVEFIRST` option, `BOF` and `EOF` are true whether the dynaset is empty or not.

When you open a dynaset, `BOF` is `False` and the first record is current. If a dynaset is empty, `BOF` and `EOF` are both true, and there is no current record.

If an `Edit` or `AddNew` operation is pending and you use one of the `Move` methods indirectly by way of the data control, then the `Update` method is invoked automatically, although, it can be stopped during the `Validate` event.

If an `Edit` or `AddNew` operation is pending and you use one of the `Move` methods directly without the data control, pending `Edit` or `AddNew` operations cause existing changes to be lost, although no error occurs.

Data is fetched from the database, as necessary, so performing a `MoveFirst` operation followed by a `MoveNext` operation incrementally builds the mirrored (cached) local set without requiring read-ahead of additional data. However, executing a `MoveLast` operation requires that the entire query be evaluated and stored locally.

When a dynaset is attached to a data control, these methods first notify the `Validate` event of the data control that record motion is about to occur. The `Validate` handler can deny the request for motion, in which case the request is ignored. If the record pointer is successfully moved, then all custom controls attached to the data control are notified automatically of the new record position.

Examples

This example demonstrates record movement within a dynaset using the `MoveFirst`, `MoveNext`, `MoveLast`, `MovePrevious` methods. Copy and paste this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

     'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")

     'Create the OraDatabase Object by opening a connection to Oracle.
     Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

     'Create the OraDynaset Object.
     Set OraDynaset = OraDatabase.CreateDynaset("select empno, ename from emp", 0&)

    MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", " & _
                 OraDynaset.Fields("ename").value

     'Move to the next record and display it.
     OraDynaset.MoveNext
     MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", " & _
                  OraDynaset.Fields("ename").value

     'Move to the last record and display it.
     OraDynaset.MoveLast
     MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", " & _
                  OraDynaset.Fields("ename").value

     'Move to the previous record and display it.
     OraDynaset.MovePrevious
     MsgBox "Employee #" & OraDynaset.Fields("empno").value & ", " & _
                 OraDynaset.Fields("ename").value

    End Sub

See Also: AddNew Method BOF Property EOF Property Edit Method EditMode Property RecordCount Property Update Method Validate Event

## MovePreviousn, MoveNextn, MoveRel, and MoveTo Methods

Applies To

OraDynaset Object

Description

Change the cursor position to the specified row within the specified dynaset.

Usage

    oradynaset.MovePreviousn offset
    oradynaset.MoveNextn offset
    oradynaset.MoveRel offset
    oradynaset.MoveTo offset

MoveNextn Method

Moves offset records forward.

MovePreviousn Method

Moves offset records backward.

MoveRel Method

Moves offset records relative to the current row. A positive value, represented by a plus (+) sign, moves the cursor down the table, and a negative value moves the cursor up the table.

MoveTo Method

Moves directly to row number offset.

Remarks

`EOF` is set when the cursor moves beyond the end of a dynaset using `MoveNextn`, `MoveRel`, or `MoveTo` methods. `BOF` is set when the cursor moves beyond the start of a dynaset using `MovePreviousn`, `MoveRel`, or `MoveTo` methods. The `MoveNextn`, `MovePreviousn`, and `MoveTo` methods accept offset as a positive integer only. The `MoveRel` methods accepts offset as either a positive or a negative integer.

The `MoveTo` `rownum` always gets the same row unless the row has been deleted. If the requested row has been deleted, the `MoveTo` method moves to the next valid row. The `MoveNextn`, `MovePreviousn`, `MoveRel`, and `MoveTo` methods do not take into account deleted rows, so be cautious when using these methods based on relative positions of row numbers.

Data Type

`Long` `Integer`

Examples

This example demonstrates the use of the `MovePreviousn`, `MoveNextn`, `MoveRel`, and `MoveTo` methods. Copy and paste this code into the definition section of a form. Then, press F5.

    Private Sub Form_Load()
      Dim OraSession As OraSession
      Dim OraDatabase As OraDatabase
      Dim OraDynaset As OraDynaset
      Dim OraFields As OraFields

      Set OraSession = CreateObject("OracleInProcServer.XOraSession")
      Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "SCOTT/TIGER", 0&)
      Set OraDynaset = OraDatabase.CreateDynaset("select * from emp where empno" & _
                 ">=7654 and empno <= 7844 ", ORADYN_NO LANKSTRIP)
      Set OraFields = OraDynaset.Fields

      'Move to 3rd record from the first record
      OraDynaset.MoveNextn 3  'Should set EOF to true
      MsgBox OraFields("ename").Value  ' Should be display SCOTT

      If OraDynaset.EOF = True Then
        MsgBox "End of the record reached"
      End If

      'Move back from the current record by the offset 2
      OraDynaset.MovePreviousn 2
      MsgBox OraFields("ename").Value  ' Should be display BLAKE

      If OraDynaset.BOF = True Then
        MsgBox "Start of the record reached"
      End If

      'Move relative in the forward direction
      OraDynaset.MoveRel 2
      MsgBox OraFields("ename").Value  ' Should be display SCOTT

      If OraDynaset.EOF = True Then
        MsgBox "End of the record reached"
      End If

      'Move relative in the backward direction
      OraDynaset.MoveRel -2
      MsgBox OraFields("ename").Value  ' Should be display BLAKE

      If OraDynaset.BOF = True Then
        MsgBox "Start of the record reached"
      End If

      'Move to the record position 4 in the current dynaset
       OraDynaset.MoveTo 4
       MsgBox OraFields("ename").Value  ' Should be display SCOTT

    End Sub

## Mul (OraIntervalDS) Method

Applies To

OraIntervalDS Object

Description

Multiplies the `OraIntervalDS` object by a multiplier.

Usage

    OraIntervalDSObj.Mul multiplier

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `multiplier` | A `Variant` for type numeric value or an `OraNumber` object to be used as the multiplier.

Remarks

The result of the operation is stored in the `OraIntervalDS` object, overwriting any previous value. There is no return value.

## Mul (OraIntervalYM) Method

Applies To

OraIntervalYM Object

Description

Multiplies the `OraIntervalYM` object by a multiplier.

Usage

    OraIntervalYMObj.Mul multiplier

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `multiplier` | A `Variant` for type numeric value or an `OraNumber` object to be used as the multiplier.

Remarks

The result of the operation is stored in the `OraIntervalYM` object, overwriting any previous value. There is no return value.

## Mul (OraNumber) Method

Applies To

OraNumber Object

Description

Multiplies the `OraNumber` object by `operand`.

Usage

    OraNumber.Mul operand

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `operand` | A `Variant` of type `String`, `OraNumber`, or a numeric value.

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

## Neg (OraIntervalDS) Method

Applies To

OraIntervalDS Object

Description

Negates the `OraIntervalDS` object.

Usage

    OraIntervalDSObj.Neg

Remarks

The result of the operation is stored in the `OraIntervalDS` object, overwriting any previous value. There is no return value.

## Neg (OraIntervalYM) Method

Applies To

OraIntervalYM Object

Description

Negates the `OraIntervalYM` object.

Usage

    OraIntervalYMObj.Neg

Remarks

The result of the operation is stored in the `OraIntervalYM` object, overwriting any previous value. There is no return value.

## Neg (OraNumber) Method

Applies To

OraNumber Object

Description

Negates an `OraNumber` object.

Usage

    OraNumber.Neg

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

## Open (OraServer) Method

Applies To

OraDatabase Object

OraServer Object

Description

Establishes a connection to an Oracle database.

Usage

    OraServer.Open serverAlias

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `serverAlias` | A `String` containing the Network alias used for connecting to the database.

Remarks

If no arguments is supplied, this method attaches to a database that was detached previously.

See Also: BeginTrans Method Close Method CommitTrans Method CreateAQ Method CreateCustomDynaset Method CreateTempBLOB/CLOB Method CreateDynaset Method CreateOraObject (OraDatabase) Method Describe Method ExecuteSQL Method FetchOraRef Method LastServerErrReset Method MonitorForFailover Method RemoveFromPool Method

## Open (OraBFILE) Method

Applies To

OraBFILE Object

Description

Opens a `BFILE`.

Usage

    OraBfile.Open

Remarks

This method should be called before accessing the `BFILE` value.

## OpenDatabase Method

Applies To

OraSession Object

OraServer Object

Description

Establishes a user session to the database. It creates a new `OraDatabase` object using the given database name, connection string, and specified options.

Usage

    Set oradatabase = orasession.OpenDatabase(database_name,connect_string, options) Set oradatabase = oraserver.OpenDatabase(connect_string, options)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`database_name` | The Oracle Network specifier used when connecting the data control to a database.
`connect_string` | The user name and password to be used when connecting to an Oracle database.
`options` | A bit flag word used to set the optional modes of the database. If `options` = `0`, the default mode settings apply. The following table shows the possible modes, which can be combined by adding their respective values.

Constants

The following table lists constants and values for the options flag.

Constant | Value | Description
--- | --- | ---
`ORADB_DEFAULT` | `&H0&` | Visual Basic Mode (Default): Field (column) values not explicitly set are set to `Null` when using the `AddNew` or `Edit` method. The `Null` values override any database column defaults. Wait on row locks when using `Edit` (`"SELECT...FOR UPDATE")`. Nonblocking SQL functionality is not enabled.
`ORADB_ORAMODE` | `&H1&` | Oracle Mode: Lets Oracle Database set the default field (column) values when using the `AddNew` method. The Oracle default column values are fetched again from database immediately after an insert or add operation. Note: If you use triggers, fetch the data again using the full Oracle Mode.
`ORADB_NOWAIT` | `&H2&` | Lock No-Wait Mode: Does not wait on row locks. When you use the `Edit` method to update a row that is locked by another user or process, Lock No-Wait mode results in an immediate return of an error code. Note: This option only applies to the `OraDynaset` object. It has no effect on `OraSQLStmt` objects or `ExecuteSQL` calls. It only raises an error in the case of a locked row.
`ORADB_NO_REFETCH` | `&H4&` | Oracle Mode (No Refetch): Performs like the Oracle Mode, but does not refetch data to the local cache. This boosts performance. Note: Use the No Refetch mode only when you intend to insert rows without editing them, because database column defaults cause inconsistencies between database data and the local cache. Attempting to edit after inserting in this mode causes a `Data` `has` `been` `modified` (`4119`) error.
`ORADB_NONBLK` | `&H8&` | Nonblocking Mode: Turns on Nonblocking mode on SQL statement execution. Nonblocking mode affects the SQL statements processed using the `ExecuteSQL`, `CreateDynaset`, or `CreateSQL` methods. Note: This feature has been deprecated.
`ORADB_ENLIST_IN_MTS` | `&H10&` | Enlist in MTS Mode: Determine whether the `OraDatabase` object enlists in the Microsoft Transaction Server (MTS) mode.
`ORADB_ENLIST_FOR_ CALLLBACK` | `&H20&` | Enlist For Callbacks Mode: Turn on the event notification. This mode has to be enabled to receive Failover Notifications.

These values can be found in the `oraconst.txt` file. For creating a valid database alias, see the Oracle Net Services Administrator's Guide.

Examples of valid `connect_string` arguments include:

- `"scott/tiger"`

- `"system/manager"`

- `"/"`

Remarks

An `OraConnection` object is created automatically and appears within the `OraConnections` collection of the session. Opening a database has the effect of opening a connection but does not perform any SQL actions.

One possible connection error that could be returned is:

    ORA-28001 "the password has expired"

The user can change the password using the `ChangePassword` method.

Examples

This example demonstrates how to programmatically create a dynaset and all of the underlying objects. Copy and paste this code into the definition section of a form with text boxes named `txtEmpNo` and `txtEName`. Then, press F5.

    Sub Form_Load ()

     'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")

     'Create the OraDatabase Object by opening a connection to Oracle.
     Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

     'Create the OraDynaset Object.
     Set OraDynaset = OraDatabase.CreateDynaset("select empno, ename from emp", 0&)

     'Display the first record.
     txtEmpNo = OraDynaset.Fields("empno").value
     txtEName = OraDynaset.Fields("ename").value

    End Sub

See Also: "Microsoft Transaction Server Support" ChangePassword (OraServer) Method "Application Failover Notifications" AddNew Method Edit Method ExecuteSQL Method ChangePassword (OraSession) Method OraConnection Object OraConnections Collection

## OriginalItem Method

Applies To

OraFields Collection

Description

Returns the `OraField` object based on the original column name used in the `SELECT` statement in the dynaset. Not available at design time and read-only at run time.

Usage

    set OraField = OraFields.OriginalItem(field_index)
    set OraField = OraFields.OriginalItem(original_name)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`field_index` | Field index of the original column name.
`original_name` | Original field name specified in the SQL statement.

Remarks

This is property is useful when a SQL statement contains `'schema.table.col'` as the `Name` of the field, and retrieves the field object specific to that original name.

Examples

The following example shows the use of the `OriginalItem` method. Copy and paste this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

     'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset
     Dim OraFields As OraFields

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")

     'Create the OraDatabase Object by opening a connection to Oracle.
     Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

     Set OraDynaset = OraDatabase.CreateDynaset("select scott.emp.deptno," & _
         "dept.deptno from scott.emp, scott.dept where dept.deptno = emp.deptno", 0&)
     'Get the Field collection object
     Set OraFields = OraDynaset.Fields

     'get the original field object. Returns "scott.emp.deptno"

     MsgBox OraField.OriginalName

     Set OraField = OraFields.OriginalItem(1)

     'Returns "dept.deptno"
     MsgBox OraField.OriginalName

    End Sub

## OriginalName

Applies To

OraField Object

Description

Returns the original column name used in the `SELECT` statement in the dynaset (as opposed to the name of the field as it appears on the server returned by the `Name` property). Not available at design time and read-only at run time.

Usage

    field_name = Orafield.OriginalName

Remarks

The `orafield.OriginalName` method returns the name of the specified `OraField` object. This returns the `Original` column name specified in the SQL statement during dynaset creation. This property is useful when a SQL statement contains `'schema.table.col'` as the `Name` of the field. It enables duplicate column names to be referenced. (Duplicate column names can be avoided by using aliases in the SQL statement.)

Examples

The following example shows the use of the `OriginalName` property. Copy and paste this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

     'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset
     Dim OraFields As OraFields

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")

     'Create the OraDatabase Object by opening a connection to Oracle.
     Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

     Set OraDynaset = OraDatabase.CreateDynaset("select scott.emp.deptno," & _
        "dept.deptno from scott.emp, scott.dept where dept.deptno = emp.deptno", 0&)

     Set OraFields = OraDynaset.Fields

     'Returns "DEPTNO"
     MsgBox OraFields(0).Name

     'Returns "scott.emp.deptno"
     MsgBox OraFields(0).OriginalName

     'Returns "dept.deptno"
     MsgBox OraFields(1).OriginalName

    End Sub

## Power (OraNumber) Method

Applies To

OraNumber Object

Description

Raises the `OraNumber` object to the power of the operand.

Usage

    OraNumber.Power operand

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `operand` | A `Variant` of type `String`, `OraNumber`, or a numeric value.

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

## Put_Value Method

Applies To

OraParamArray Object

Description

Inserts values into the table parameter.

Usage

    OraParamArray.Put_Value(value, index)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `value` | A `Variant` representing the value to insert.
[`in`] `index` | An `Integer` representing the index value of the object.

Remarks

This method should be used to insert a value before accessing a row in a table. A row does not contain a valid value until a row is assigned a value. Any reference to an unassigned row in the table raises an `OLE` `Automation` `error`.

The `value` argument can be an Oracle Database 10g object, such as an `OraBLOB`. For `Put_Value`, a copy of the object is made at that point in time, and `Get_Value` must be accessed to obtain a new object that refers to that index value. For example, if `iotype` is `ORATYPE_BOTH` and an `OraBLOB` obtained from a dynaset is passed in as the input value, `Get_Value` needs to be called after the SQL has been executed to obtain the newly updated output value of the `ParamaterArray`.

Similar to a dynaset, the object obtained from `ParamaterArray` `Get_Value` method always refers to the latest value for that `ParamaterArray` index. The Visual Basic value `Null` can also be passed as a value. The Visual Basic value `EMPTY` can be used for `BLOB` and `CLOB` to indicate an empty LOB, and for `OBJECT`, `VARRAY` and `NESTED` `TABLE` to indicate an object whose attributes are all `Null`.

When binding to `RAW` columns (`ServerType` `ORATYPE_RAW_BIN`) value should be a byte array.

## Read (OraLOB/BFILE) Method

Applies To

OraBLOB, OraCLOB Objects

OraBFILE Object

Description

Reads into a buffer a specified portion of a `BLOB`, `CLOB`, or `BFILE` value. Returns the total amount of data read.

Usage

    amount_read = OraBlob.Read buffer, chunksize
    amount_read = OraClob.Read buffer, chunksize
    amount_read = OraBfile.Read buffer, chunksize

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`out`] `buffer` | `Variant` of type character array for `OraCLOB`, `Variant` of type byte array for `OraBLOB`, or `OraBFILE` from which the piece is read.
[`in`] [optional] `chunksize` | An `Integer` specifying the amount to be read. Default value is the size of the LOB. In bytes for `OraBLOB` or `OraBFILE`; characters for `OraCLOB`.
[`out`] `amount_read` | An `Integer` representing the total amount of data read. In bytes for `OraBLOB` or `OraBFILE`; characters for `OraCLOB`.

Remarks

Reads the LOB or `BFILE` data from the offset specified by the `Offset` property. For multiple piece read operation, the `PollingAmount` property must be set to the value of the total amount of data to be read, and the `Status` property must be checked for the success of each piece operation.

Note: When reading a portion of a LOB, it is recommended that you set the `PollingAmount` property, rather than using the `chunksize` parameter. This avoids the possibility of raising an error if the entire LOB is not read before to executing another LOB method.

Examples

Be sure that you have installed the OraLOB Schema Objects as described in "Schema Objects Used in LOB Data Type Examples" .

Example: Multiple-Piece Read of a LOB

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim OraDynaset As OraDynaset
    Dim PartImage As OraBlob
    Dim chunksize As Long
    Dim AmountRead As Long
    Dim buffer As Variant
    Dim buf As String

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb","scott/tiger", 0&)

    'Create the OraDynaset Object.
    Set OraDynaset = OraDatabase.CreateDynaset("select * from part", 0&)

    'Get OraBlob from OraDynaset
    Set PartImage = OraDynaset.Fields("part_image").Value

    'Set Offset and PollingAmount property for piecewise Read operation
    PartImage.offset = 1
    PartImage.PollingAmount = PartImage.Size
    chunksize = 50000

    'Get a free file number
    FNum = FreeFile

    'Open the file
    Open "image.dat" For Binary As #FNum

    'Do the first read on PartImage, buffer must be a variant
    AmountRead = PartImage.Read(buffer, chunksize)

    'put will not allow Variant type
    buf = buffer
    Put #FNum, , buf

    ' Check for the Status property for polling read operation
    While PartImage.Status = ORALOB_NEED_DATA
        AmountRead = PartImage.Read(buffer, chunksize)
        buf = buffer
        Put #FNum, , buf
    Wend

    Close FNum

Example: Single-Piece Read of a LOB

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim PartDesc As OraClob
    Dim AmountRead As Long
    Dim buffer As Variant
    Dim buf As String

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'Add PartDesc as an Output parameter and set its initial value.
    OraDatabase.Parameters.Add "PartDesc", Null, ORAPARM_OUTPUT
    OraDatabase.Parameters("PartDesc").ServerType = ORATYPE_CLOB

    'Execute the statement returning 'PartDesc'
    OraDatabase.ExecuteSQL ("BEGIN select part_desc into :PARTDESC from" & _
                 "part where part_id = 1 for update NOWAIT; END;")

    'Get 'PartDesc' from Parameters collection
    Set PartDesc = OraDatabase.Parameters("PartDesc").Value

    'Get a free file number
    FNum = FreeFile

    'Open the file.

    Open "Desc.Dat" For Binary As #FNum

    'Read entire CLOB value, buffer must be a Variant
    AmountRead = PartDesc.Read(buffer)

    'put will not allow Variant type
    buf = buffer
    Put #FNum, , buf

    Close FNum

See Also: Offset (OraLOB/BFILE) Property PollingAmount Property Status (OraLOB/BFILE) Property

## ReadChunk Method

Applies To

OraField Object

Description

Returns a `String` containing the bytes of all or a portion of a `LONG` or `LONG` `RAW` field.

Usage

    data_string = orafield.ReadChunk(offset, numbytes, bytesread)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`offset` | The number of bytes in the field to skip before copying data.
`numbytes` | The number of bytes to copy.
`bytesread` | The number of bytes read.

Remarks

The `ReadChunk` method behaves like the `GetChunk` method, but it returns the actual number of bytes read in the `bytesread` argument.

See Also: "Migration from LONG RAW to LOB or BFILE" GetChunk Method

## Refresh Method

Applies To

OraDynaset Object

OraSQLStmt Object

Description

Forces an immediate update of the dynaset given the current `Connect`, `DatabaseName`, and SQL properties.

Forces an immediate update of the dynaset by reexecuting the SQL statement in the SQL statement object.

Usage

    oradynaset.Refresh
    oradynaset.DbRefresh
    orasqlstmt.Refresh
    orasqlstmt.DbRefresh

Remarks

This method cancels all edit operations (`Edit` and `AddNew` methods), executes the current contents of the SQL statement buffer, and moves to the first row of the resulting dynaset. Any dynaset objects created before issuing the `Refresh` method, including bookmarks, record counts, and field collections, are considered invalid. The `OraConnection` and `OraSession` objects associated with the previous dynaset remain unchanged.

Performing a refresh operation with this method can be more efficient than refreshing with a data control. This method also lets you execute a modified SQL statement without creating a new dynaset or `OraSQLStmt` object.

The preferred refresh methods when changing parameter values are `oradynaset.Refresh` or `orasqlstmt.Refresh`, because required database operations are minimized (SQL parsing, binding, and so on). This can improve performance when only parameter values have changed.

If you call the `Refresh` method after assigning an invalid SQL statement to the SQL property of a dynaset or SQL statement object, these objects remain valid. However, a dynaset in this state does not permit any row or field operations. Bound controls also exhibit unusual behaviors similar to those that occur when the standard Visual Basic data control `RecordSource` is set to an invalid SQL statement at run time and then refreshed.

You can regain the normal dynaset and SQL statement operations by refreshing the object with a valid SQL statement. The `Refresh` method treats `Null` or empty SQL statements as invalid.

Examples

Refresh Method Example (OraDynaset)

This example demonstrates the use of parameters, the `Refresh` method, and the SQL property to restrict selected records. Copy and paste this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

     'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")

     'Create the OraDatabase Object by opening a connection to Oracle.
     Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

     'Create a parameter with an initial value.
     OraDatabase.Parameters.Add "job", "MANAGER", 1

     'Create the OraDynaset Object.
     Set OraDynaset =OraDatabase.CreateDynaset("select * from emp where job=:job",0&)

     'Notice that the SQL statement is NOT modified.
     MsgBox OraDynaset.SQL

     'Currently, OraDynaset only contains employees whose job is MANAGER.
     'Change the value of the job parameter.

     OraDatabase.Parameters("job").Value = "SALESMAN"

     'Refresh the dynaset.
     OraDynaset.Refresh

     'Currently, OraDynaset only contains employees whose job is SALESMAN.
     'Notice that the SQL statement is NOT modified.
     MsgBox OraDynaset.SQL

     'Remove the parameter.
     OraDatabase.Parameters.Remove ("job")

     End Sub

Refresh Method Example (OraSQLStmt)

This example demonstrates the use of parameters, the `Refresh` method, and the SQL property for the . object. Copy and paste this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

     'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraSqlStmt As OraSQLStmt

     'Create the OraSession Object.
     Set OraSession = CreateObject("OracleInProcServer.XOraSession")

     'Create the OraDatabase Object by opening a connection to Oracle.
     Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

     OraDatabase.Parameters.Add "EMPNO", 7369, 1
     OraDatabase.Parameters("EMPNO").ServerType = 2  'ORATYPE_NUMBER
     OraDatabase.Parameters.Add "ENAME", 0, 2
     OraDatabase.Parameters("ENAME").ServerType = 1  'ORATYPE_VARCHAR2

     Set OraSqlStmt = OraDatabase.CreateSQL("Begin Employee.GetEmpName (:EMPNO," & _
                  ":ENAME); end;", 0&)

     'Notice that the SQL statement is NOT modified.
     MsgBox OraSqlStmt.SQL

     'Should display SMITH
     MsgBox OraDatabase.Parameters("ENAME").Value

     'Change the value of the empno parameter.
     OraDatabase.Parameters("EMPNO").Value = 7499

     'Refresh the dynaset.
     OraSqlStmt.Refresh

     'Should display ALLEN
     MsgBox OraDatabase.Parameters("ENAME").Value

     'Notice that the SQL statement is NOT modified.
     MsgBox OraSqlStmt.SQL

     'Remove the parameter.
     OraDatabase.Parameters.Remove ("job")

     End Sub

See Also: AddNew Method Connect Property CreateDynaset Method DatabaseName Property Edit Method OraConnection Object OraDynaset Object OraSession Object SQL Property RecordSource Property

## Refresh (OraRef) Method

Applies To

OraRef Object

Description

Refreshes the referenceable object from the most current database snapshot.

Usage

    OraRef.Refresh

## Register Method

Applies To

OraSubscription Object

Description

Activates the subscription.

Usage

    orasubscription.Register

Remarks

When the specified database event is fired, the `NotifyDBevents` method of the `dbevent` handler that was passed in while creating this subscription is invoked.

Examples

See "Example: Registering an Application for Notification of Database Events" for a complete example.

See Also: "Database Events" OraSubscription Object OraSubscriptions Collection

## Remove Method

Applies To

OraParameters Collection

Description

Removes a parameter from the `OraParameters` collection.

Usage

    oraparameters.Remove(member_name)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`member_name` | A `Variant` specifying an integer subscript from `0` to `Count` `1`, or the parameter name.

Remarks

Instead of repeatedly removing and adding unwanted parameters, use the `AutoBindDisable` and `AutoBindEnable` methods.

For an `OraParameter` of type `ORATYPE_CURSOR`, this method destroys the dynaset object associated with the cursor, and clears the local cache temporary files.

Examples

See "Example: ExecuteSQL".

See Also: Add Method AutoBindDisable Method AutoBindEnable Method OraDatabase Object OraParameter Object OraParameters Collection

## Remove (OraSubscriptions Collection) Method

Applies To

OraSubscriptions Collection

Description

Removes a subscription from the `OraSubscriptions` collection.

Usage

    orasubscriptions.Remove(member)

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`member` | A `Variant` specifying an integer subscript from `0` to `Count`, or the subscription name.

Remarks

This method unregisters (removes) the subscription if it is active, and destroys the subscription associated with it.

See Also: Add (OraSubscriptions Collection) Method OraSubscription Object OraSubscriptions Collection

## RemoveFromPool Method

Applies To

OraDatabase Object

Description

Removes the `OraDatabase` object from the pool.

Usage

    OraDatabase.RemoveFromPool

Remarks

This method applies only to those `OraDatabase` objects that are retrieved from the pool using the `GetDatabaseFromPool` method.

No exceptions or errors are raised if the `OraDatabase` object is not a member the pool.

This method is useful for removing `OraDatabase` objects from the pool whose connections are no longer valid.

See Also: CreateDatabasePool Method DestroyDatabasePool Method GetDatabaseFromPool Method

## ResetTrans Method

Applies To

OraConnection Object

OraSession Object

Description

Unconditionally rolls back all transactions and clears the transaction mode initiated by `BeginTrans` method.

Usage

    oraconnection.ResetTransorasession.ResetTrans

Remarks

This method does not generate events or produce errors. Because the `ResetTrans` method does not generate events, you cannot cancel the `ResetTrans` method in a `Validate` event, as you can with a rollback or commit operation.

Note: If an `OraDatabase` object has been enlisted with Microsoft Transaction Server (MTS) and is part of a global MTS transaction, this method has no effect.

Examples

This example demonstrates the use of the `BeginTrans` and `ResetTrans` methods to group a set of dynaset edits into a single transaction. Copy and paste this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

    'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
    'Create the OraDynaset Object.
    Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)

     'Start Transaction processing.
     OraDynaset.Session.BeginTrans

     'Traverse until EOF is reached, setting each employee's salary to zero.
     Do Until OraDynaset.EOF
       OraDynaset.Edit
       OraDynaset.Fields("sal").value = 0
       OraDynaset.Update
       OraDynaset.MoveNext
     Loop
     MsgBox "All salaries set to ZERO."

     'Currently, the changes have NOT been committed to the database.
     'End Transaction processing.
     'Using ResetTrans means the rollback cannot be canceled in the Validate event.
     OraDynaset.Session.ResetTrans
     MsgBox "Salary changes rolled back."

    End Sub

See Also: BeginTrans Method CommitTrans Method OraSession Object Rollback Method Microsoft Transaction Server Support Validate Event

## Rollback Method

Applies To

OraConnection Object

OraDatabase Object

OraSession Object

Description

Ends the current transaction and rolls back all pending changes to the database.

Usage

    oraconnection.Rollback
    orasession.Rollback
    oradatabase.Rollback

Remarks

When this method is invoked, all `OraDynaset` objects that share the specified session or connection are given the opportunity to cancel the rollback request. If they do not cancel the request, they are advised when the rollback succeeds.

This feature is useful primarily for dynasets that are created as part of an Oracle Data Control operation. For these dynasets, the `Validate` event is sent to allow them to cancel the rollback request.

- `OraConnection` and `OraDatabase`:

  The `Rollback` method rolls back all pending transactions within the specified connection. This method has no effect if a transaction has not begun. When a session-wide transaction is in progress, you can use this call to prematurely roll back the transactions for the specified connection.

- `OraSession`:

  The `Rollback` method rolls back all pending transactions within the specified session. The `Rollback` method is valid only when a transaction has been started. If a transaction has not been started, the use of the `Rollback` method results in an error.

Note: If an `OraDatabase` object has been enlisted with Microsoft Transaction Server (MTS) and is part of a global MTS transaction, this method has no effect.

Examples

This example demonstrates the use of the `BeginTrans` and `Rollback` methods to group a set of dynaset edits into a single transaction. Copy and paste this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

    'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
    'Create the OraDynaset Object.
    Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)

     'Start Transaction processing.
     OraDynaset.Session.BeginTrans

     'Traverse until EOF is reached, setting each employee's salary to zero.
     Do Until OraDynaset.EOF
       OraDynaset.Edit
       OraDynaset.Fields("sal").value = 0
       OraDynaset.Update
       OraDynaset.MoveNext
     Loop
     MsgBox "All salaries set to ZERO."

     'Currently, the changes have NOT been committed to the database.
     'End Transaction processing.
     OraDynaset.Session.Rollback
     MsgBox "Salary changes rolled back."

    End Sub

See Also: AutoCommit Property BeginTrans Method CommitTrans Method OraSession Object OraConnection Object ResetTrans Method "Microsoft Transaction Server Support" Validate Event

## Round (OraNumber) Method

Applies To

OraNumber Object

Description

Rounds the `OraNumber` object to the specified decimal place.

Usage

    OraNumber.Power decplaces

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `decplaces` | An `Integer` specifying the number of digits to the right of the decimal point from which to round. Negative values are allowed and signify digits to the left of the decimal point.

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

## SetPi (OraNumber) Method

Applies To

OraNumber Object

Description

Sets an `OraNumber` object to Pi.

Usage

    OraNumber.SetPi

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

## Sin (OraNumber) Method

Applies To

OraNumber Object

Description

Calculates the sine of an `OraNumber` object given in radians.

Usage

    OraNumber.Sin

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

## Sqrt (OraNumber) Method

Applies To

OraNumber Object

Description

Calculates the square root of an `OraNumber` object.

Usage

    OraNumber.Sqrt

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

This method returns an error if the `OraNumber` object is less than zero.

## Sub (OraIntervalDS) Method

Applies To

OraIntervalDS Object

Description

Subtracts an argument from the `OraIntervalDS` object.

Usage

    OraIntervalDSObj.Sub operand

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `operand` | A `Variant` of type `String`, a numeric value, or an `OraIntervalDS`, object to be subtracted.

Remarks

The result of the operation is stored in the `OraIntervalDS` object, overwriting any previous value. There is no return value.

If `operand` is a `Variant` of type `String`, it must be in the following format: [+/-] Day HH:MI:SSxFF.

If `operand` is a numeric value, the value provided should represent the total number of days that the constructed `OraIntervalDS` object represents.

## Sub (OraIntervalYM) Method

Applies To

OraIntervalYM Object

Description

Subtracts an argument from the `OraIntervalYM` object.

Usage

    OraIntervalYMObj.Sub operand

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `operand` | A `Variant` of type `String`, a numeric value, or an `OraIntervalYM` object to be subtracted.

Remarks

The result of the operation is stored in the `OraIntervalYM` object, overwriting any previous value. There is no return value.

If `operand` is a `Variant` of type `String`, it must be in the following format: [+/-] YEARS-MONTHS.

If `operand` is a numeric value, the value provided should represent the total number of years that the constructed `OraIntervalYM` object represents.

## Sub (OraNumber) Method

Applies To

OraNumber Object

Description

Subtracts a numeric argument from the `OraNumber` object.

Usage

    OraNumber.Sub operand

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `operand` | A `Variant` of type `String`, type `OraNumber`, or a numeric value.

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

## Tan (OraNumber) Method

Applies To

OraNumber Object

Description

Calculates the tangent of an `OraNumber` object given in radians.

Usage

    OraNumber.Tan

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

## ToDate Method

Applies To

OraTimeStamp Object

OraTimeStampTZ Object

Description

Returns a copy of the `Date` type from an `OraTimeStamp` or `OraTimeStampTZ` object.

Usage

    Set date = OraTimeStampObj.ToDate
    Set date = OraTimeStampTZObj.ToDate

Remarks

This method returns the datetime values in the `Date` data type. As a result, the date-time values can be adjusted if they fall outside the range allowed by a VB date.

- For an `OraTimeStamp` object:

  Returns a new `Date` object with the same date-time values as the current `OraTimeStamp` object, but the nanosecond portion is truncated.

- For an `OraTimeStampTZ` object:

  Returns a new `Date` object with the same date-time values as the current `OraTimeStampTZ` object, but the nanosecond portion and time zone portion are truncated.

Examples

Using the OraTimeStamp Object

    Dim OraTimeStamp As OraTimeStamp

    ...
    'Create OraTimeStamp using a string
    Set OraTimeStamp = OraSession.CreateOraTimeStamp("1999-APR-29 12:10:23.444 AM", _
           "YYYY-MON-DD HH:MI:SS.FF AM")

    ' returns a Date type with date value set to "1999-APR-29 12:10:23 AM"
    ' note that the fractional part is dropped
    Set date = OraTimeStamp.ToDate

Using the OraTimeStampTZ Object

    Dim OraTimeStampTZ As OraTimeStampTZ

    ...
    'Create OraTimeStampTZ using a string
    Set OraTimeStampTZ = OraSession.CreateOraTimeStampTZ("2000-12-28" & _
            "12:10:23.444 -07:00", "YYYY-MM-DD HH:MI:SS.FF TZH:TZM")

    'returns a Date type with date value set to "2000-12-28 12:10:23"
    'note that Time Zone and nanosecond portions are dropped
    Set date = OraTimeStampTZ.ToDate

## ToOraNumber (OraIntervalDS) Method

Applies To

OraIntervalDS Object

Description

Returns an `OraNumber` object containing a value that represents the total number of days that the `OraIntervalDS` object specifies.

Usage

    Set OraNumberObj = OraIntervalDSObj.ToOraNumber

## ToOraTimeStamp Method

Applies To

OraTimeStampTZ Object

Description

Returns a copy of the `OraTimeStamp` object that has the date-time value in the specified time zone of the current `OraTimeStampTZ` object.

Returns a copy of the `OraTimeStamp` object from an `OraTimeStampTZ` object.

Usage

    Set OraTimeStampObj = OraTimeStampTZObj.ToOraTimeStamp

Remarks

Returns a new `OraTimeStamp` object that has the date-time values in the specified time zone of the current `OraTimeStampTZ` object.

Examples

    Dim OraTimeStampTZ As OraTimeStampTZ

    ...
    'Create OraTimeStampTZ using a string
    Set OraTimeStampTZ = OraSession.CreateOraTimeStampTZ("2000-12-28" & _
           "12:10:23.444 -07:00", "YYYY-MM-DD HH:MI:SS.FF TZH:TZM")

    'returns a new OraTimeStamp object with date value equal to
    ' "2000-12-28 12:10:23.444"
    'note that Time Zone portion is dropped
    Set OraTimeStamp = OraTimeStampTZ.ToOraTimeStamp

## ToOraTimeStampLTZ Method

Applies To

OraTimeStampTZ Object

Description

Returns a copy of the `OraTimeStamp` object that has the date-time value normalized to the session time zone of the current `OraTimeStampTZ` object.

Usage

    Set OraTimeStampObj = OraTimeStampTZObj.ToOraTimeStampLTZ

Remarks

Returns a new `OraTimeStamp` object that has the date-time values normalized to the session time zone of the current `OraTimeStampTZ` object.

Examples

    Dim OraTimeStampTZ As OraTimeStampTZ

    ...
    'Create OraTimeStampTZ using a string
    Set OraTimeStampTZ = OraSession.CreateOraTimeStampTZ("2003-APR-29" & _
            "12:00:00 -07:00", "YYYY-MON-DD HH:MI:SS TZH:TZM")

    'Assuming that the Session Time Zone is "-08:00"
    'returns a new OraTimeStamp object with date value normalized to
    'session Time Zone, "2003-APR-29 11:00:00"

    Set OraTimeStamp = OraTimeStampTZ.ToOraTimeStampLTZ
    ...

## ToOraTimeStampTZ Method

Applies To

OraTimeStamp Object

Description

Returns a copy of the `OraTimeStampTZ` object from an `OraTimeStamp` object.

Usage

    Set OraTimeStampTZObj = OraTimeStampObj.ToOraTimeStampTZ

Remarks

Returns a new `OraTimeStampTZ` object with the same date-time values as the current `OraTimeStamp` object. The time zone information in the returned `OraTimeStampTZ` object is set to the session time zone.

Examples

    Dim OraTimeStamp As OraTimeStamp

    ...
    'Create OraTimeStamp using a string
    Set OraTimeStamp = OraSession.CreateOraTimeStamp("1999-APR-29" & _
             "12:10:23.444 AM", "YYYY-MON-DD HH:MI:SS.FF AM")

    ' assuming that the session Time Zone is "-07:00" returns a new
    ' OraTimeStampTZ object with date value equal to "1999-APR-29 12:10:23 -07:00"
    Set OraTimeStampTZ = OraTimeStamp.ToOraTimeStampTZ

## ToUniversalTime Method

Applies To

OraTimeStampTZ Object

Description

Returns a copy of the `OraTimeStampTZ` object that has the date-time value normalized to Coordinated Universal Time (UTC) of the current `OraTimeStampTZ` object.

Usage

    Set OraTimeStampTZObj1 = OraTimeStampTZObj.ToUniversalTime

Remarks

Returns a new `OraTimeStampTZ` object that has the date-time values normalized to the UTC of the current `OraTimeStampTZ` object.

Note: UTC was formerly known as Greenwich Mean Time.

Examples

    Dim OraTimeStampTZ As OraTimeStampTZ
    Dim OraTimeStampTZ_UTC As OraTimeStampTZ
    ...
    'Create OraTimeStampTZ using a string
    Set OraTimeStampTZ = OraSession.CreateOraTimeStampTZ("2003-APR-29 " & _
            "12:00:00 -07:00", "YYYY-MON-DD HH:MI:SS TZH:TZM")

    'returns a new OraTimeStampTZ object with date value normalized to
    'UTC time, "2003-APR-29 19:00:00 00:00"
    Set OraTimeStampTZ_UTC = OraTimeStampTZ.ToUniversalTime
    ...

## Trim (OraCollection) Method

Applies To

OraCollection Object

Description

Trims a given number of elements from the end of the collection.

Usage

    OraCollection.Trim size

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `size` | An `Integer` specifying the number of elements to trim.

Remarks

The elements are removed from the end of the collection. An error is returned if the size is greater than the current size of the collection.

Examples

The following example illustrates the `Trim` method. Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in OraCollection Examples" .

Example: Trim Method for the OraCollection Object

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim EnameList as OraCollection

    'create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create a dynaset object from department
    set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&)

    'retrieve a Enames column from Department.
    'Here Value property of OraField object returns EnameList OraCollection
    set EnameList = OraDynaset.Fields("Enames").Value

    'display the size of the collection
    msgbox  EnameList.Size

    'Trim the EnameList collection by one. Before that row level
     'lock should be obtained

    OraDynaset.Edit
    EnameList.Trim 1
    OraDynaset.Update

    'display the new size of the collection
    msgbox EnameList.Size

## Trim (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

Description

Trims or truncates the LOB value to shorter length.

Usage

    OraBlob.Trim NewLen
    OraClob.Trim NewLen

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `NewLen` | An `Integer` specifying the new length of the LOB value; must be less than or equal to the current length.

Remarks

Either a row-level lock or object-level lock should be obtained before calling this method.

Note: When manipulating LOBs using LOB methods, such as the `Write` and `CopyFromFile`, the LOB object is not automatically trimmed if the length of the new data is shorter than the old data. Use the `Trim` (OraLOB) method to shrink the LOB object to the size of the new data.

## Trunc (OraNumber) Method

Applies To

OraNumber Object

Description

Truncates an Oracle number at a specified decimal place.

Usage

    OraNumber.Trunc decplaces

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
[`in`] `decplaces` | An `Integer` specifying the number of digits to the right of the decimal point from which to truncate. Negative values are allowed and signify digits to the left of the decimal point.

Remarks

The result of the operation is stored in the `OraNumber` object. There is no return value.

## Unregister Method

Applies To

OraSubscription Object

Description

Unregisters this subscription, which turns off notifications on the specific database event.

Usage

    orasubscription.UnRegister

Remarks

Unregistering a subscription ensures that the user does not receive notifications related to that subscription or database event in the future. If the user wants to resume notification, then the only option is to re-register the subscription.

Examples

Registering an Application for Notification of Database Events Example

See "Example: Registering an Application for Notification of Database Events".

See Also: "Database Events" OraSubscription Object OraSubscriptions Collection

## Update Method

Applies To

OraDynaset Object

Description

Saves the copy buffer to the specified dynaset.

Usage

    oradynaset.Update
    oradynaset.DbUpdate

Remarks

The `Update` method completes an `AddNew` or `Edit` operation and immediately commits changes to the database unless a `BeginTrans` operation is pending for the session.

Once the `Update` method is called on a given row in a dynaset in a global transaction (that is, a `BeginTrans` operation is issued), locks remain on the selected rows until a `CommitTrans` or `Rollback` method is called.

The mirrored data image is also updated so that the query does not have to be reevaluated to continue browsing and updating data. The method used for updating the mirror image is subject to the options flag that was passed to the `OpenDatabase` method that created the `OraDatabase` object of this dynaset.

If this dynaset is attached to a data control, then the `Validate` event of the data control code may optionally cancel the update request. If the update completes, then all bound controls associated with the dynaset are notified of the update so they can reflect the data changes automatically.

Examples

This example demonstrates the use of `AddNew` and `Update` methods to add a new record to a dynaset. Copy and paste this code into the definition section of a form. Then, press F5.

    Sub Form_Load ()

    'Declare variables
     Dim OraSession As OraSession
     Dim OraDatabase As OraDatabase
     Dim OraDynaset As OraDynaset

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'Create the OraDynaset Object.
    Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)

    'Begin an AddNew.
    OraDynaset.AddNew

    'Set the field(column) values.
    OraDynaset.Fields("EMPNO").Value = "1000"
    OraDynaset.Fields("ENAME").Value = "WILSON"
    OraDynaset.Fields("JOB").Value = "SALESMAN"
    OraDynaset.Fields("MGR").Value = "7698"
    OraDynaset.Fields("HIREDATE").Value = "19-SEP-92"
    OraDynaset.Fields("SAL").Value = 2000
    OraDynaset.Fields("COMM").Value = 500

    OraDynaset.Fields("DEPTNO").Value = 30

    'End the AddNew and Update the dynaset.
    OraDynaset.Update

    End Sub

See Also: AddNew Method Edit Method OpenDatabase Method OraDatabase Object Validate Event

## Update (OraRef) Method

Applies To

OraRef Object

Description

Flushes the modified referenceable object to the database.

Usage

    OraRef.Update

Remarks

The `Update` method completes the `Edit` operation and commits the changes to the database unless a `BeginTrans` operation is pending for the session.

Examples

The following example updates the attributes of the `PERSON` referenceable object in the database. Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in the OraObject and OraRef Examples".

Updating Attribute Values: Dynaset Example

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim Person as OraRef

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create a dynaset object from customers
    set OraDynaset = OraDatabase.CreateDynaset("select * from customers", 0&)

    'retrieve a aperson column from customers. Here Value property of OraField
    'object returns Person OraRef
    set Person = OraDynaset.Fields("aperson").Value

    'locks the Person object in the server for modifying its attributes
    Person.Edit
      Person.Name = "Eric"
      Person.Age = 35

    'Update method flushes the modified referenceable object in the server
    Person.Update

Updating Attribute Values: Parameter Example

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim Person  as OraRef

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create an  OraParameter object represent Address object bind Variable
    OraDatabase.Parameters.Add "PERSON", Null, ORAPARM_OUTPUT, ORATYPE_REF,"PERSON"

    'execute the sql statement which selects person from the customers table
    OraDatabase.ExecuteSQL ("BEGIN select aperson into :PERSON from customers" & _
                      "where account = 10; END;")

    'get the Person object from OraParameter
    set Person = OraDatabase.Parameters("PERSON").Value

    'locks the Person object in the server for modifying its attributes
    Person.Edit
      Person.Name = "Eric"
      Person.Age = 35

    'Update method flushes the modified referenceable object in the server
    Person.Update

## Write (OraLOB) Method

Applies To

OraBLOB, OraCLOB Objects

Description

Writes a buffer into the `BLOB` or `CLOB` value of this object and returns the total amount of the data written.

Usage

    amount_written = OraBlob.Write buffer, chunksize, piece
    amount_written = OraClob.Write buffer, chunksize, piece

Arguments

The arguments for the method are:

Arguments | Description
--- | ---
`in`] `buffer` | The character array for an `OraCLOB` object or byte array for the `OraBLOB` object from which the piece is written.
[`in`] [optional] `chunksize` | An `Integer` specifying the length of the buffer, in characters for an `OraCLOB` object and bytes for an `OraBLOB` or `OraBFILE` object. Default value is the size of the buffer argument.
[`in`] [optional] `piece` | An `Integer` specifying which piece of the buffer is being written. Possible values include: `ORALOB_ONE_PIECE` - Buffer is written in a single piece. This is the default. `ORALOB_FIRST_PIECE` - Buffer represents the piece of LOB data to be written. `ORALOB_NEXT_PIECE` - Buffer represents the next piece of LOB data to be written. `ORALOB_LAST_PIECE` - Buffer represents the last piece of LOB data to be written.
[`out`] `amount_written` | An `Integer` representing the amount written, in characters for an `OraCLOB` object and bytes for an `OraBLOB` or `OraBFILE` object.

Remarks

Obtain either a row-level lock or object-level lock before calling the `Write` method. This method writes the `BLOB` or `CLOB` data from the offset specified by the `Offset` property. For a multiple-piece write operation, the `PollingAmount` property can be set to the value of the total amount of data to be written, and the `Status` property must be checked for the success of each piece operation. If the total amount is not known, then the `PollingAmount` property can be set to `0` and polling still occurs as long as the piece type is not `OraLob_piece`.

For the last piece, set the piece argument to `ORALOB_LAST_PIECE`. You must write the polling amount in bytes or characters. It is not possible to terminate the `Write` operation early if the `PollingAmount` property is not zero.

When the OraLOB `Pollingamount` = `0` but the piece type on OraLOB `Write` is not `ORALOB_ONE_PIECE`, polling still occurs. Polling completes when `ORALOB_LAST_PIECE` is sent as an argument to a call to the `Write` method. This is useful when calling the `OraCLOB.Write` method in a variable-width character set, when counting the total amount of characters ahead of time may be costly.

Note: When manipulating LOBs using LOB methods, such as the `Write` and `CopyFromFile`, the LOB object is not automatically trimmed if the length of the new data is shorter than the old data. Use the `Trim` (OraLOB) method to shrink the LOB object to the size of the new data.

Examples

Be sure that you have installed the OraLOB Schema Objects as described in "Schema Objects Used in LOB Data Type Examples" .

Multiple-Piece Write of a LOB Example

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim OraDynaset As OraDynaset
    Dim PartDesc As OraClob
    Dim buffer As String
    Dim chunksize As Long
    Dim amount_written As Long

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'Create the OraDynaset Object
    Set OraDynaset = OraDatabase.CreateDynaset("select * from part", 0&)
    Set PartDesc = OraDynaset.Fields("part_desc").Value
    chunksize = 32000

    'Re adjust the buffer size
    buffer = String$(chunksize, 32)
    FNum = FreeFile

    'Open the file.
    Open "partdesc.dat" For Binary As #FNum

    'set the offset and PollingAmount properties for piece wise
    'Write operation
    PartDesc.offset = 1
    PartDesc.PollingAmount = LOF(FNum)
    remainder = LOF(FNum)

    'Lock the row for write operation
    OraDynaset.Edit
    Get #FNum, , buffer

    'Do first write operation
    amount_written = PartDesc.Write(buffer, chunksize, ORALOB_FIRST_PIECE)

    While PartDesc.Status = ORALOB_NEED_DATA
      remainder = remainder - chunksize
      If remainder < chunksize Then
        piecetype = ORALOB_LAST_PIECE
        chunksize = remainder
       Else
        piecetype = ORALOB_NEXT_PIECE
      End If
      Get #FNum, , buffer

      amount_written = PartDesc.Write(buffer, chunksize, piecetype)
    Wend

    Close FNum

    'call Update method to commit the transaction
    OraDynaset.Update

Single-Piece Write of a LOB Example

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim PartImage As OraBlob
    Dim buffer() As Byte

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'Add PartDesc as an Output parameter and set its initial value.
    OraDatabase.Parameters.Add "PartImage", Null, ORAPARM_OUTPUT
    OraDatabase.Parameters("PartImage").ServerType = ORATYPE_BLOB

    'Begin the transaction
    OraSession.BeginTrans

    'Execute the statement returning 'PartDesc'
    OraDatabase.ExecuteSQL ("BEGIN select part_image into :PARTIMAGE" & _
                "from part where part_id = 1 for update NOWAIT; END;")

    'Get 'PartDesc' from Parameters collection
    Set PartImage = OraDatabase.Parameters("PartImage").Value

    'Get a free file number
    FNum = FreeFile

    'Open the file.
    Open "PartImage.Dat" For Binary As #FNum

    'Re adjust the buffer size to hold entire file data
    ReDim buffer(LOF(FNum))
    Get #FNum, , buffer

    'Do one write operation
    amount_written = PartImage.Write(buffer)

    Close FNum
    MsgBox "Amount written to the LOB data is " & amount_written

    'Ends the transaction
    OraSession.CommitTrans

See Also: Offset (OraLOB/BFILE) Property PollingAmount Property Status (OraLOB/BFILE) Property Trim (OraLOB) Method Writing LOB Data